March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
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?
@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])
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
148 | |
92 | |
72 | |
58 |