Monthly Rolling in Power BI

[Anonymous]

Hi,

I am very new in Power BI.

I am trying to calculate rolling sales margin by month in Power BI.

I am using below formula:

SM% rolling = calculate(sum(T2[SM(k)])/sum(T2[Rev(k)]),

                       filter(T3,T3[Month]<=max(T3[Month])&&

                       T3[Month]>(max(T3[Month])-12)))

But when I am crossedchecked the above values in a table. it seems the value for 2nd quarter i.e for 2017 Q2, it is only considering months - 4,5,6 but not all months i.e. 1 to 6. I think I have to add one filter which will ignore Quarter selection. But at the same time I have to show values for 'Quarter' dimension.

I will aprreciate if anyone can help me on this.

Thanks!

Regards,

Poonam

SM RollingIt give 75% SM

[v-piga-msft]

Hi  @Anonymous,

Have you solved your problem with the replies?

If you have, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output, so that we can help further investigate on it?

Best Regards,

Cherry

[Ashish_Mathur]

Hi,

Try this measure

=CALCULATE(sum(T2[SM(k)])/sum(T2[Rev(k)]),DATESYTD(Calendar[Date],"31/12/")

There should be a Calendar Table and a relationship from the Date column  of the T2 table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas for extracting Year, Quarter and Month.  In the visual, drag the Year/Month/Quarter from the Calendar Table.

Hope this helps.

[CheenuSing]

Hi @Anonymous

Can you share some data or the pbix in onedrive or google share drive and provide the link to find a solution.

Cheers

CheenuSing