Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Regards,
Pavan
Solved! Go to 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.
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
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.
@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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
98 | |
38 | |
37 |
User | Count |
---|---|
154 | |
120 | |
73 | |
73 | |
63 |