Dax Running Total In by Non Date and showing each year Running Total In

[WarrenBarrell]

Hello all, I am trying to calculate the running total in calculated on DAX, by Month on Book column and year (as in my example).

I've presented the inputs and what I am aiming for in an output table below. 

Any help would be highly appreciated. 

Input

Date	Month on Book	Loss Amount
1/01/2019	1	500
1/02/2019	2	1000
1/03/2019	3	1500
1/04/2019	4	2000
1/05/2019	5	2500
1/06/2019	6	3000
1/07/2019	7	3500
1/08/2019	8	4000
1/09/2019	9	4500
1/10/2019	10	5000
1/11/2019	11	5500
1/12/2019	12	6000
1/01/2020	13	6500
1/02/2020	14	7000
1/03/2020	15	7500
1/04/2020	16	8000
1/05/2020	17	8500
1/06/2020	18	9000
1/07/2020	19	9500
1/08/2020	20	10000
1/09/2020	1	10500
1/10/2020	2	11000
1/11/2020	3	11500
1/12/2020	4	12000
1/01/2021	5	12500
1/02/2021	6	13000
1/03/2021	7	13500
1/04/2021	8	14000
1/05/2021	9	14500
1/06/2021	10	15000
1/07/2021	11	15500
1/08/2021	12	16000
1/09/2021	13	16500
1/10/2021	14	17000
1/11/2021	15	17500

Output

Month on Book	2019	2020	2021
1	500	10500	0
2	1500	21500	0
3	3000	33000	0
4	5000	45000	0
5	7500	45000	12500
6	10500	45000	25500
7	14000	45000	39000
8	18000	45000	53000
9	22500	45000	67500
10	27500	45000	82500
11	33000	45000	98000
12	39000	45000	114000
13		51500	130500
14		58500	147500
15		66000	165000
16		74000
17		82500
18		91500
19		101000
20		111000

[VahidDM · Accepted solution]

Hi @WarrenBarrell 

Try to create a Measure with this code:

**bleep** Loss Amount = 
VAR _A =
    CALCULATE (
        MAX ( 'Table'[Month on Book ] ),
        REMOVEFILTERS ( 'Table'[Date].[Year] )
    )
VAR _B =
    CALCULATE (
        MAX ( 'Table'[Month on Book ] ),
        ALLEXCEPT ( 'Table', 'Table'[Date].[Year] )
    )
VAR _C =
    CALCULATE (
        SUM ( 'Table'[Loss Amount] ),
        FILTER ( ALL ( 'Table'[Month on Book ] ), 'Table'[Month on Book ] <= _A )
    ) + 0
RETURN
    IF ( _A <= _B, _C, 0 )

then create a matrix with this arangment:

output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[WarrenBarrell]

thank you very much Vahid! You have clearly explained and demonstrated this.

[amitchandak]

@WarrenBarrell , I think you need YTD. Use date table, Day, Month , year in visual should come from date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

or a new column

Day of Year =datediff([Year Start date] , [Date],Day) +1

measure

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
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.