Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
123 | |
107 | |
61 | |
55 |