3 Month Rolling creates "new" months

[Gugge]

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.

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

[amitchandak · Accepted solution]

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

[Gugge]

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.