How to calculate previous 12 month total based on user month selection?

[romilv1]

Hello All,

When user selects say year as 2025 and month as January it should show past 12 months of data (i.e. data from Feb 2024 to Jan 2025). We have dim_date table and we want to show sum of revenue.

What should be the dax? We also want to include "USE RELATIONSHIP" as the relationship is not active between Revenue table and dim_date table?

Thanks

[mariussve1 · Accepted solution]

I was little to fast, dateadd just shows the value on that point, but rolling 12m we need to use DATESINPERIOD():

Rolling 12m = 
DATESINPERIOD(Dim_date[Date], MAX(Dim_date[Date]), -12, MONTH)

[romilv1]

Yes got the solution from other posts. But thanks for this. It helped!

[mariussve1]

Hi,

Im having relative_month in my date table, then it would be:

Calculate( Sum ( 'Table'[Column] ), USERELATIONSHIP(), 'Date'[Relative_month] = -12 )

In the USERELATIONSHIP() you need to adjust to the columns you have connected in the inactive relationship.

Instead of relative_month you can use DATEADD(), but for it to work the date table need to be marked as date table first.

[romilv1]

Does this look correct? I use this and the chart went blank!

[mariussve1]

Hi,

Are you able to share your model with me, or a smaller part of it that is relevant?

[romilv1]

Does this look correct? This doesn't bring values

Expected value

Date_Add value

[mariussve1]

Im sorry, we need to create rolling 12 months.

are you able to share your model? If not I Can try create a demo model for you to show 🙂

[romilv1]

Dim_date is already a date table.