3, 6 & 12 months rolling percentage

[Anonymous]

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 %.

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'))

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

Any assistance on this is much appreciated.

PBIX file

Data File 

Regards,

Pavan

[Anonymous]

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]

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 · Accepted solution]

@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.