cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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.

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

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)
 Date Revenue R12 2022-01 100 100 2022-02 0 100 2022-03 200 300 2022-04 0 300

Thanks!

1 ACCEPTED SOLUTION
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!
5 REPLIES 5
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!
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) )

Helper I

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."

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.

Thanks

Solution Sage

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``````

Helper I

Looks like the one that I posted 🙂

`_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)))

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors