cumulative monthly total

[TapZxK]

Hi Folks, 

I have written a measure that calculates cumulative monthly totals.

Total Rolling Expenses =
CALCULATE ( [Total Historical Expenses], DATESYTD ( 'Calendar'[Date], "8/31" ) )

Everything appears ok but i'm getting numbers for the future dates.

Any ideas how to remove values for the months that do not have the data yet. 

In the example below the data is available until month of January (Including) but as you can see the line extends until end of year with the same values as the values in January. 

BR,

TapZxK

[Anonymous]

Hi, cool solution, but then the yearly total shows Blank() as well. Any idea how to still include the totals when using the IF function?

[amitchandak]

@TapZxK , You can have measures like

Total Rolling Expenses =
CALCULATE ( [Total Historical Expenses], DATESYTD ( 'Calendar'[Date], "8/31" ), 'Calendar'[Date]<=today() )

 or

Total Rolling Expenses =
if(max('Calendar'[Date]<=today()), CALCULATE ( [Total Historical Expenses], DATESYTD ( 'Calendar'[Date], "8/31" ), blank())

or in place of today use this from you fact Table

var _today = maxx(allselected(Fact), Fact[Date])

[TapZxK]

Hi @amitchandak, 

Thanks for your prompt reply as always. 
I tried what You suggested. Unfortunately, it didn't work.

I'm still getting the same results. See Screenshots below. 

There are active 1 - To - Many relationships between Calendate[Date] -> Expenses[Date] Tables. 

When trying the below measure I'm getting an error, not sure why though. 

Total Rolling Expenses =
if(max('Calendar'[Date]<=today()), CALCULATE ( [Total Historical Expenses], DATESYTD ( 'Calendar'[Date], "8/31" ), blank())

Best Regards,

TapZxK

[v-easonf-msft · Accepted solution]

Hi, @TapZxK 

You need to check if the parentheses match. The measure should be changed as:

Total Rolling Expenses =
IF (
    MAX ( 'Calendar'[Date] ) <= TODAY (),
    CALCULATE ( [Total Historical Expenses], DATESYTD ( 'Calendar'[Date], "8/31" ) ),
    BLANK ()
)

You can also create a measure as below and apply it to visual filter pane to filter data.

filter =
IF ( MAX ( 'Calendar'[Date] ) <= TODAY (), 1, 0 )

Best Regards,
Community Support Team _ Eason