How to get an aggregated value for each week

[yve214]

Hi Guys I have a problem I am unable to solve.

I am trying to get an aggregated value for the week that is monday - sunday. 

How am I able to aggregate it so it shows just for the last day of the week.

sample table:

Assuming by week is Monday - Sunday. Please I want to aggregate all quantities for each week and show it on the weekend date (Sunday of each week which is the max date)

Category	Date	Qty	Expected result
a	1/3/2022	100	-
a	1/4/2022	150	-
a	1/5/2022	50	-
a	1/6/2022	300	-
a	1/7/2022	100	-
a	1/8/2022	25	-
a	1/9/2022	0	725

[yve214]

@tackytechtom ,

Thank you very much. It worked, I think I was using the wrong date table so it was giving me different aggregates.

@Icey thank you so much for verifying. I had to look at the query again with a fresh pair of eyes

[yve214]

@tackytechtom ,

Thank you so much for your help. I was able to get an aggregated sum for sundays but it wasnt the actual sum for each week (monday through sunday). Please what can I adjust?

Category	Date	Qty	Expected result
a	1/3/2022	100	-
a	1/4/2022	150	-
a	1/5/2022	50	-
a	1/6/2022	300	-
a	1/7/2022	100	-
a	1/8/2022	25	-
a	1/9/2022	0	725
a	1/10/2022	101	-
a	1/11/2022	85	-
a	1/12/2022	100	-
a	1/13/2022	150
a	1/14/2022	100	-
a	1/15/2022	55	-
a	1/16/2022	0	591

[Icey]

Hi @yve214 ,

Have you created a measure, not a column, based on @tackytechtom 's expression? Based on my test, it can give you the expected result.

Best Regards,

Icey

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

[tackytechtom · Accepted solution]

Hi @yve214 ,

How about this:

Here the DAX code for the measure:

WeekTotalMeasure = 
VAR _currentWeek = WEEKNUM (  MAX ( Table[Date] ), 2 )
RETURN
IF (
    WEEKDAY ( MAX ( Table[Date] ), 2) = 7,
    CALCULATE (
        SUM ( Table[Qty] ),
        ALL ( Table),
        WEEKNUM ( Table[Date] ) = _currentWeek
    ), 
    BLANK()
)

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/