Next 3 month forecast by month

[Whitney]

Good morning,

I'm fairly new to Power Bi and have only recently started to use dax.

Our managers are forecasting labour availability indicating excess or extra labour for their workshops. I am attempting to portray this with a 3-month forward view that will update when each month rolls over. For example, being in December, month 1 = Jan, month 2 = Feb, month 3 = Mar; in Jan next month, month 1 = Feb, month 2 = Mar and so on.

Seen below, I have one bar chart for each of the 3-months showing the +/- labour by the week beginning date of the respective month, and the workshop name (removed from the image). I have used the relative date filtering for 'in the next 1 calendar month' however, this logic does not apply to month's 2 & 3 as the results are cumulative. I'm struggling with this because the month I am summing will be different each month as it rolls over. I assume it will be something like 'next 2 calendar months minus the next 1 calendar month' however, I'm struggling to find the right dax functions to do this for me.

Any assistance is greatly appreciated, thank you in advanced!

[amitchandak · Accepted solution]

Check if these can solve your purpose

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
Next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],1,MONTH)))
Next MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],1,MONTH))))

2nd Next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],2,MONTH)))
2nd Next MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],2,MONTH))))

3rd Next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],3,MONTH)))
3rd Next MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],3,MONTH))))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[amitchandak]

Not sure I got it completely. But you can next three-month data like

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),3,MONTH))  

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Whitney]

Thanks for your reply @amitchandak 

Unfortunately, 3-months rolling will not be suitable for us in this case because we would like to see the next 3-months results individually so we can make decisions on our labour pool for each respective month. There is also no specific start or end date (as such) because the next 3 months will change depending on what month we are in. Hope this is a bit clearer?

[amitchandak]

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.

[Whitney]

@amitchandak apologies for the delay. Hope you're enjoying the festive season. Let me know if this link doesn't work https://drive.google.com/open?id=1E6p8_pPka9a_8l-OdfwYp1IKg3YTygYy

Essentially is a rolling 3 month period in that the 3 month period is always rolling however, I'd like to see each month individually instead of the summation of the 3 months.

Appreciate your assistance.

[amitchandak · Accepted solution]

Check if these can solve your purpose

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
Next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],1,MONTH)))
Next MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],1,MONTH))))

2nd Next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],2,MONTH)))
2nd Next MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],2,MONTH))))

3rd Next MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],3,MONTH)))
3rd Next MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],3,MONTH))))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Whitney · Accepted solution]

Month 1 and month 2 are working, thank you! However, month 3 seems to be producing random results. For example, the first column are the figures for month 3 and the second column is the formula result:

Column1                                Column2

0,0,1,1                                     -2

-1,3-1,-2                                  -2

3,-1,-4,-2                                 -6

-4,5,2,2                                    1

I've validated all my results for months 1 and 2 with zero incorrect summations.

Below formulas were used:

Month1Forecast = CALCULATE(SUM('Appended Week Beginning Date'[Forecast Actual]),DATESMTD(ENDOFMONTH(dateadd('Appended Week Beginning Date'[Forecast Beginning Date],1,MONTH))))

Month2Forecast = CALCULATE(SUM('Appended Week Beginning Date'[Forecast Actual]),DATESMTD(ENDOFMONTH(dateadd('Appended Week Beginning Date'[Forecast Beginning Date],2,MONTH))))

Month3Forecast = CALCULATE(SUM('Appended Week Beginning Date'[Forecast Actual]),DATESMTD(ENDOFMONTH(dateadd('Appended Week Beginning Date'[Forecast Beginning Date],3,MONTH))))

Any ideas? @amitchandak 

[amitchandak]

The only doubt I have now is of having non regular dates. Try this. Create a calendar table and join it with [Forecast Beginning Date] and use the calendar date in formula.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

[Whitney]

Thank you @amitchandak works perfectly!