Repeated/Running/Trending sum on day level

[alwalao]

Hello everyone,

I am new to dax and would like some help on writing dax.

I need to create a table with repeated/running/trending sum on day level. I have two table (dimCal & fct_tble) where fct_tble is on weekly level. 

dimCalendar (for a month of oct)

Date

10/01/2022

10/02/2022

10/03/2022

fct_table (Excluding the In-Progress stage and taking only ClosedWon)

AsOfDate	Id	Amt_hst	Stg_name
10/07/2022	1	10000	ClosedWon
10/07/2022	2	5000	ClosedWon
10/14/2022	3	15000	In-Progress
10/14/2022	1	10000	ClosedWon
10/14/2022	2	5000	ClosedWon
10/21/2022	4	2500	ClosedWon
10/21/2022	1	10000	ClosedWon
10/21/2022	2	5000	ClosedWon
10/28/2022	5	5000	ClosedWon
10/28/2022	1	10000	ClosedWon
10/28/2022	2	5000	ClosedWon

Desire result (On day level) - I skipped some days but i am sure you guys get what i meant.

Date	Amt_hst	Stg_name
10/01	15000	ClosedWon
10/02	15000	ClosedWon
10/03	15000	ClosedWon
10/07	15000	ClosedWon
10/20	15000	ClosedWon
10/21	17500	ClosedWon
10/22	17500	ClosedWon
10/28	22500	ClosedWon
10/29	22500	ClosedWon
10/30	22500	ClosedWon

As for now i wasn't able to get the result as per above. 

Here is dax that i tried:

Trending =

var d = MAX(dimDate[Date])

var m = CALCULATE(MAX(v_p[AsOfDate]), v_p[AsOfDate]<=d)

var s = CALCULATE([DistinctSum], FILTER(v_p, v_p[StageName__hst] = "Closed Won"), v_p[AsOfDate]=m)

return s

 Thanks in advance. 

[Anonymous]

Hi @alwalao ,

How's 22500 on 10/28, 10/29 and 10/30? From what I am understanding, it should be 20000.

If my understanding is wrong, please explain the calculation logic.

Best Regards,

Stephen Tao

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

[alwalao]

Hi @Anonymous 

My bad, i apologize for that. I missed out Id = 4 in the sum process for 10/28/2022. The fct_tble is a snapshot table so it wont leave out any rows. Desire result still remain the same

Here is the new fct_tble.

AsOfDate	Id	Amt_hst	Stg_name
10/07/2022	1	10000	ClosedWon
10/07/2022	2	5000	ClosedWon
10/14/2022	3	15000	In-Progress
10/14/2022	1	10000	ClosedWon
10/14/2022	2	5000	ClosedWon
10/21/2022	4	2500	ClosedWon
10/21/2022	1	10000	ClosedWon
10/21/2022	2	5000	ClosedWon
10/28/2022	5	5000	ClosedWon
10/28/2022	1	10000	ClosedWon
10/28/2022	2	5000	ClosedWon
10/28/2022	4	2500	ClosedWon

Thanks for the reply.