Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Gugge
Helper I
Helper I

3 Month Rolling creates "new" months

Hi, I'm new to PowerBI, I'm trying to do a three month rolling sales chart.

I've managed to get it to work, but not as I want it to.

 

First, I've created a DateTable using:

PBIDate = calendar(firstdate(SalesData[Posting_Date]),lastdate(SalesData[Posting_Date]))

This seems to work to always only show first and last date in my extracted data.

 

I have then for my three month rolling used the following formula (I've used both MAX and LASTDATE);

Measure = calculate(sum(SalesData[Sales_Amount_Actual]),

datesinperiod(PBIDate[Date],

MAX(PBIDate[Date]),-3,month))+0

 

The formula works fine for the period where I have data (Nov-18 until Jun-20). But for some reason, it also creates numbers for jan-18 until dec-20. How do I change this so it only calculates for the months that I have data.Capture.PNG

(Green line shows 3MR, Black is AVR 3MR, red is actual each month)

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Gugge , +0 forces complete left. So remove that. In Visual use type = continuous

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Gugge , +0 forces complete left. So remove that. In Visual use type = continuous

Thank you @amitchandak 

That's why, that solution works to remove the month before and after. But it ruins my line chart as all month within the period with no data ss not shown as zero.

 

I realized I can lock start date at the X-axis to begin when my data begins so atleast that issue is "solved". But it seems I need to choose between showing months with no data as zeros and have the future month included.

Or show the months without data as a line from last month with data to next month with data and show data until last month.

 

For me it makes more sense to have an empty month as zero, it seems more logic.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.