Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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