Measure to calculate Running sum of n rows for each group

[ank47]

I have this data with Group and Values. I want to sum "Values" on a rolling window of 4 rows sorted by "Date". I want this calculation seperately for each "Group". 

Date	Group	Value	Result
2/1/2022	1	0
2/2/2022	1	1
2/3/2022	1	0
2/4/2022	1	1	2 (0+1+0+1)
2/5/2022	1	1	3 (1+0+1+1)
2/1/2022	2	0
2/2/2022	2	1
2/3/2022	2	0
2/4/2022	2	1	2
2/5/2022	2	1	3

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Value measure: = 
SUM( Data[Value] )

Four rows rolling sum: =
VAR fourlatestdays =
    TOPN (
        4,
        FILTER (
            ALL ( Data ),
            Data[Group] = MAX ( Data[Group] )
                && Data[Date] <= MAX ( Data[Date] )
        ),
        Data[Date], DESC
    )
VAR result =
    CALCULATE (
        [Value measure:],
        FILTER ( ALL ( Data ), Data[Group] = MAX ( Data[Group] ) ),
        fourlatestdays
    )
RETURN
    IF (
        HASONEVALUE ( Data[Date] ),
        IF ( COUNTROWS ( fourlatestdays ) < 4, BLANK (), result )
    )

[ank47]

Thanks for helping me out!

Now i am trying to use this rolling sum in a card visual,Can you also help me with my query below?

I am using a dashboard to track latest data from "Rolling_sum" column. This "Rolling_sum" column is Whole number data type. Whenever i am trying to use it as a card it shows aggregation. I just want to show the latest information without any aggregation just like any text column.

I have tried creating a new measure which duplicates the "Rolling_sum" column using "FIXED" to convert it into text. But, using it in a card dosent allow me to choose "First" or "Last" values.