[SOLVED] Cumulative Total, missing month

Anything related to PowerPivot and DAX Formuale
Bernard Heymans
Posts: 34
Joined: Wed Feb 28, 2018 12:18 am

[SOLVED] Cumulative Total, missing month

Postby Bernard Heymans » Thu Nov 14, 2019 2:08 am

Hi

I created a measure that calculate the sum as from begining of time...

Qty Apples Cumul = CALCULATE( [Qty Apple] ,
FILTER( ALL('Calendar'[DDay]) ,
'Calendar'[DDay] <= max(Apples[date] )
)
)


BUT if there is no entry for one month, it will not show a cumulated result.

see red cell for december 2017 & july 2018 in attached exemple.


Note: I don't want to work on TotalYTD as my total sould run over years.


Thanks for your help.

Bernard.
Attachments
Question missing month cumul.xlsx
(470.23 KiB) Downloaded 27 times
Last edited by Bernard Heymans on Thu Nov 14, 2019 8:14 pm, edited 1 time in total.

Bernard Heymans
Posts: 34
Joined: Wed Feb 28, 2018 12:18 am

Re: Cumulative Total, missing month

Postby Bernard Heymans » Thu Nov 14, 2019 8:14 pm

SOLVED....

QtyCherry Cumul :=

VAR laatstedag = EOMONTH( CALCULATE( MAX( Cherry[date] ); ALL( Cherry));0)

return
IF( MAX('Calendar'[DDay] ) <=laatstdag ;
CALCULATE( [Qty Cherry] ; FILTER( ALL( 'Calendar'[DDay] ); 'Calendar'[DDay] <= max( 'Calendar'[DDay]))))
Attachments
Question missing month cumul.xlsx
(475.88 KiB) Downloaded 25 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 3 guests