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,
I've been working on some rolling 12 months graphs. Most things are working well, but when diving into the details there's always something not working as expected.
Top matrix show the R12 revenues for different products. And bottom table shows revenue per month and R12 revenues for the choosen product. My problem is that it doesn't show the R12-value for months with 0 revenue.
If I choose the setting "show information wihtout data", then I achieve this, which is not what I want
My current measure:
Date | Revenue | R12 |
2022-01 | 100 | 100 |
2022-02 | 0 | 100 |
2022-03 | 200 | 300 |
2022-04 | 0 | 300 |
Solved! Go to Solution.
I tried with this:
I tried with this:
@Puttsson , if you use period/month from a date table in visual
and use measure like
CALCULATE([Revenue Sum Switch],
DATESINPERIOD(DimDate[Date],max(DimDate[Date]),-12,MONTH) )
Yes, thanks @amitchandak , exactly, that solves one part as I wrote, but creates another issue.
The reason why I don't want it for future months is because I want to show R12 on historical data (Revenue R12), then a prognosis for the future data, which is a separate measure. This separate measaure is done with ISBLANK, and only shown when Revenue R12 is empty.
Thanks
Hi @Puttsson
You could add an IF statement around your calculation to check for future months
Something like:
Revenue R12 =
VAR _MaxDate = MAX(DimDate[Date])
VAR _LastCompleteMonth = EOMONTH(TODAY(), -1)
VAR _Result =
IF( _MaxDate <= _LastCompleteMonth,
CALCULATE([Revenue Sum Switch],
DATESINPERIOD(DimDate[Date],max(DimDate[Date]),-12,MONTH)
),
BLANK()
)
RETURN
_Result
Looks like the one that I posted 🙂
Using LASTDATE(ALL(vFactRM[RevenueDate])) instead of yours
_LastCompleteMonth = EOMONTH(TODAY(), -1)
since last month is dynamic. Could be June, or could be May as of today.
,BLANK()
, CALCULATE([Revenue Sum Switch],DATESINPERIOD(DimDate[Date],max(DimDate[Date]),-12,MONTH)))
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 |
---|---|
26 | |
20 | |
18 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
24 | |
22 | |
21 |