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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Anonymous
Not applicable

3, 6 & 12 months rolling percentage

Hi All,

I have a requirement of getting 3, 6  and 12 months rolling % from the below data.

I did check multiple solutions provided in the forum.  I was able to arrive close the required output, but still missing the exact %.

 

Screenshot.PNG

 

Below is the required output.  I have used the below excel formula to arrive the %

=IF(AND('Total Header Forecast'=0,'Primary Sales'=0),0,IF('Total Header Forecast'=0,1,TAE/'Total Header Forecast'))

Screenshot_2.PNG

i have attached the excel and pbix file for your reference.

Any assistance on this is much appreciated.

PBIX file

Data File 

Regards,

Pavan

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , for rolling you can use date table and formula similar to these

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

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

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @amitchandak ,

 

I just changed the sum column in the formula provided by you with the excel formula that was used earlier, unbelivable, it has given me the exact right output.

I was thinking till this moment that i have to use a standard column "CALCULATE(sum(Sales[Sales Amount])," always to get what we want, however i just realised and learned that it can be tweaked as per the requirements.

 

I am really sorry for the inconvenience if any to the forum members.

 

To conclude, with the below formulas, i have the required solution.

 

Regards,

Pavan

Anonymous
Not applicable

Hi @amitchandak ,

 

Thank your for your time.

I did try these formulas, however they have not given me the right output.

At present i have used the below DAX formula which is closest to my required output.

3MR =
VAR datamonth = MAX('Fact Table'[Date])
VAR datayear = MAX('Fact Table'[Date])
VAR temp =
CALCULATE(
AVERAGEX(
SUMMARIZE(
'Fact Table',
'Date Calendar'[Date].[Year],
'Date Calendar'[Date].[Month],
"Sumtotal", [FB%]
),
[Sumtotal]
),
DATESINPERIOD(
'Date Calendar'[Date],
LASTDATE('Date Calendar'[Date]),
-3,
MONTH
)
)
RETURN
IF(AND(SELECTEDVALUE('Date Calendar'[Date].[Year]) = datayear,
SELECTEDVALUE('Date Calendar'[Date].[MonthNo]) > datamonth), BLANK(), temp)
 
I did tried with SUMX instead of Averagex, but still the same result.
 
Regards,
Pavan
amitchandak
Super User
Super User

@Anonymous , for rolling you can use date table and formula similar to these

 

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

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

 

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 :radacad sqlbi My Video Series Appreciate your Kudos.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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