Rolling 12 months Sum for each month in a table visual

[antoinepal]

Hello,

I have a table visual with date, date year-1 and sum of sales. I would like to have an extra column-measure to show sum of sales fro the previous 12 months for each date in the table. Below is a screenshot of how the table visual looks like.

I tried several measures but seems impossible.

Can you help?

[amitchandak]

@antoinepal , try with a date calendar

example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[fhill]

Try this as a Calcualted Column...  

SUM_DateDiff =

VAR _pyear = 'Table'[Date-1Y]

RETURN

CALCULATE( SUM ('Table'[Sales]), FILTER( ALL ( 'Table'), 'Table'[Date] <= EARLIER('Table'[Date]) && 'Table'[Date] >= _pyear))

[antoinepal]

Thank you for your response.

For each date i would like  to have the sales for 12 months period.

For example, for 1/5/2018 i would like to have sales from 1/5/2017 to 1/5/2018

For 1/4/2018 i would like to have sales from 1/4/2017 to 1/4/2018 etc. This for each date in the visual.

I believe a measure needed as this is a table visual. The data in the query have different structure.

Thanks once again!

[v-deddai1-msft · Accepted solution]

Hi @antoinepal ,

If you need a measure , you can just use the measure below:

Measure =
VAR _pyear = MAX('Table'[Date-1Y])
VAR _current = MAX('Table'[Date])
RETURN
CALCULATE( SUM ('Table'[Sales]), ALL ( 'Table'), 'Table'[Date] <= _current && 'Table'[Date] >= _pyear)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai