Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

cumulative monthly total

Hi Folks,    I have written a measure that calculates cumulative monthly totals.   Total Rolling Expenses = CALCULATE ( [Total Historical Expenses], DATESYTD ( 'Calendar'[Date], "8/31" ) )   E...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

Anonymous , 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])

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

    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