[SOLVED] Cumulative Total, missing month

Anything related to PowerPivot and DAX Formuale
Bernard Heymans
Posts: 28
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 6 times
Last edited by Bernard Heymans on Thu Nov 14, 2019 8:14 pm, edited 1 time in total.

Bernard Heymans
Posts: 28
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 7 times


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests

cron