cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Puttsson
Helper I
Helper I

Rolling 12 months with "empty months"

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.

Puttsson_0-1657198223460.png

 

If I choose the setting "show information wihtout data", then I achieve this, which is not what I want

Puttsson_1-1657198628127.png

 

 

My current measure:

CALCULATE([Revenue Sum Switch],
DATESINPERIOD(DimDate[Date],max(DimDate[Date]),-12,MONTH),
FILTER(ALL(vFactRM[RevenueDate]),vFactRM[RevenueDate]<=max(vFactRM[RevenueDate])))
 
If I remove the last part (FILTER(ALL...)), then I will get data R12-data for future months which I don't want.
 
So what I want to achieve
  • R12 for all months that has any data at all (in my dataset it's currently to 2022-05)
DateRevenueR12
2022-01100100
2022-020100
2022-03200300
2022-040300

 

Thanks!

 

1 ACCEPTED SOLUTION
Puttsson
Helper I
Helper I

I tried with this:

IF(max(DimDate[Date])>LASTDATE(ALL(vFactRM[RevenueDate]))
    ,BLANK()
   ,
CALCULATE([Revenue Sum Switch],DATESINPERIOD(DimDate[Date],max(DimDate[Date]),-12,MONTH)))
 
 
And from what I see now it seems to solve my problem. Not sure if it's a good solution though.. And maybe breaks other things.
 
Input is very much appreciated!

View solution in original post

5 REPLIES 5
Puttsson
Helper I
Helper I

I tried with this:

IF(max(DimDate[Date])>LASTDATE(ALL(vFactRM[RevenueDate]))
    ,BLANK()
   ,
CALCULATE([Revenue Sum Switch],DATESINPERIOD(DimDate[Date],max(DimDate[Date]),-12,MONTH)))
 
 
And from what I see now it seems to solve my problem. Not sure if it's a good solution though.. And maybe breaks other things.
 
Input is very much appreciated!
amitchandak
Super User
Super User

@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) )

 



Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Yes, thanks @amitchandak , exactly, that solves one part as I wrote, but creates another issue.

"If I remove the last part (FILTER(ALL...)), then I will get data R12-data for future months which I don't want."
 
Puttsson_0-1657265304887.png
 
 
So maybe it's the correct solution, but how can I get rid of R12 for future months?

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.

Puttsson_1-1657265363175.png

 


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.

 

IF(max(DimDate[Date])>LASTDATE(ALL(vFactRM[RevenueDate]))

    ,BLANK()
   , 
CALCULATE([Revenue Sum Switch],DATESINPERIOD(DimDate[Date],max(DimDate[Date]),-12,MONTH)))

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors