Calculate running total for last 12 months

[mazeed92]

In PowerBI, I am bit struggling in writing a calculated column query to find the running total for last 12 month per Region.

For Example,

for US calculation should be done for 08/1/2023 - 09/1/2022 (last 12 months)
for UK should be done for 08/01/2024 - 09/1/2023 (last 12 months)

RunningTotal=Calculate(Sum(Value),datesinperiod(date,Max(date),-12,month),region = Earlier(region)

Max(Date) is picking the maximum date from the entire column instead of picking the maximum date per region. Please help me on this.

Thanks

Sampledata:

Region	Date	Value
US	6/1/2022	1
US	7/1/2022	2
US	8/1/2022	3
US	9/1/2022	4
US	10/1/2022	5
US	11/1/2022	6
US	12/1/2022	7
US	1/1/2023	8
US	2/1/2023	9
US	3/1/2023	10
US	4/1/2023	10
US	5/1/2023	10
US	6/1/2023	10
US	7/1/2023	10
US	8/1/2023	10
UK	6/1/2023	1
UK	7/1/2023	2
UK	8/1/2023	3
UK	9/1/2023	4
UK	10/1/2023	5
UK	11/1/2023	6
UK	12/1/2023	7
UK	1/1/2024	8
UK	2/1/2024	9
UK	3/1/2024	10
UK	4/1/2024	10
UK	5/1/2024	10
UK	6/1/2024	10
UK	7/1/2024	10
UK	8/1/2024	10
Canada	5/1/2024	10
Canada	6/1/2024	10
Canada	7/1/2024	10
Canada	8/1/2024	10

[FarhanJeelani · Accepted solution]

Hi  @mazeed92 ,

It looks like you're on the right track but hitting a snag with the Max(Date) picking the overall maximum rather than the maximum per region. The issue stems from using Max(Date) in  the Calculate function without respecting the context of the region.

You need to ensure that the Max(Date) is calculated per region, not globally. The way to fix this is by using ALLEXCEPT to remove the context for everything except the Region column. This will allow Max(Date) to operate within the context of each region.

Here’s the corrected DAX formula for the calculated column:

RunningTotal = 
CALCULATE(
    SUM('Table'[Value]),
    DATESINPERIOD(
        'Table'[Date], 
        MAXX(FILTER('Table', 'Table'[Region] = EARLIER('Table'[Region])), 'Table'[Date]), 
        -12, 
        MONTH
    ),
    ALLEXCEPT('Table', 'Table'[Region])
)

Example Calculation:

For the US, the running total for a given month (say August 2023) will include all values from August 2023 to August 2022. For the UK, it will calculate the running total for August 2024 back to August 2023.

Ensure that your Date column is formatted correctly as a Date type.

Make sure that your data model has a Date table that’s related to your main table for time intelligence functions to work properly. If not, you may need to create one.

Please mark this as solution if it helps you . Appreciate Kudos.

[sevenhills · Accepted solution]

I am not sure whether you want the max of running total or each row with running total.

If it is max/sum then use @mazeed92  query above.

If you want every row running total of past 12 months, use this

RT - Past 12 months = Calculate(Sum('Table'[Value]), 'Table'[Region] = EARLIER('Table'[Region]), DATESINPERIOD( 'Date Table'[Date], 'Table'[Date], -12, MONTH), ALLEXCEPT('Table', 'Table'[Region]) )
-- Below also works the same way!
-- Calculate(Sum('Table'[Value]), 'Table'[Region] = EARLIER('Table'[Region]), DATESINPERIOD( 'Table'[Date], 'Table'[Date], -12, MONTH), ALLEXCEPT('Table', 'Table'[Region]) )

I added Total by Region for my own understanding, ignore as it is not your request. The red line where I marked in the below pic shows visually the effect of RT - 12 months ... 

[Anonymous · Accepted solution]

Hi All,
Firstly  sevenhills and  FarhanJeelani thank you for yours solutions!

And @mazeed92 ，On the basis of the previous two SU answers, let's try to use some other functions to help you realize your requirements, let me share what I did, I hope it can be helpful to you, we use EMonth for 12 months of data acquisition.

RunningTotal (column) = 
VAR CurrentRegion = 'Table'[Region]           
VAR CurrentDate = 'Table'[Custom]             
VAR MaxDatePerRegion = 
    CALCULATE(
        MAX('Table'[Custom]),                
        ALLEXCEPT('Table', 'Table'[Region])  
    )
VAR StartDate = EOMONTH(MaxDatePerRegion, -12) + 1  
VAR EndDate = MaxDatePerRegion                      
RETURN
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Region] =CurrentRegion     
        && 'Table'[Custom] >= StartDate          
        && 'Table'[Custom] <= EndDate            
    )
)

If you have any other questions, you can check out the pbix file I uploaded, I hope it helps, and I'd be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[sevenhills · Accepted solution]

I am not sure whether you want the max of running total or each row with running total.

If it is max/sum then use @mazeed92  query above.

If you want every row running total of past 12 months, use this

RT - Past 12 months = Calculate(Sum('Table'[Value]), 'Table'[Region] = EARLIER('Table'[Region]), DATESINPERIOD( 'Date Table'[Date], 'Table'[Date], -12, MONTH), ALLEXCEPT('Table', 'Table'[Region]) )
-- Below also works the same way!
-- Calculate(Sum('Table'[Value]), 'Table'[Region] = EARLIER('Table'[Region]), DATESINPERIOD( 'Table'[Date], 'Table'[Date], -12, MONTH), ALLEXCEPT('Table', 'Table'[Region]) )

I added Total by Region for my own understanding, ignore as it is not your request. The red line where I marked in the below pic shows visually the effect of RT - 12 months ... 

[FarhanJeelani · Accepted solution]

Hi  @mazeed92 ,

It looks like you're on the right track but hitting a snag with the Max(Date) picking the overall maximum rather than the maximum per region. The issue stems from using Max(Date) in  the Calculate function without respecting the context of the region.

You need to ensure that the Max(Date) is calculated per region, not globally. The way to fix this is by using ALLEXCEPT to remove the context for everything except the Region column. This will allow Max(Date) to operate within the context of each region.

Here’s the corrected DAX formula for the calculated column:

RunningTotal = 
CALCULATE(
    SUM('Table'[Value]),
    DATESINPERIOD(
        'Table'[Date], 
        MAXX(FILTER('Table', 'Table'[Region] = EARLIER('Table'[Region])), 'Table'[Date]), 
        -12, 
        MONTH
    ),
    ALLEXCEPT('Table', 'Table'[Region])
)

Example Calculation:

For the US, the running total for a given month (say August 2023) will include all values from August 2023 to August 2022. For the UK, it will calculate the running total for August 2024 back to August 2023.

Ensure that your Date column is formatted correctly as a Date type.

Make sure that your data model has a Date table that’s related to your main table for time intelligence functions to work properly. If not, you may need to create one.

Please mark this as solution if it helps you . Appreciate Kudos.