topic Re: How to get an aggregated value for each week in DAX Commands and Tips

How to get an aggregated value for each week

yve214 — Fri, 13 May 2022 20:11:00 GMT

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

Re: How to get an aggregated value for each week

tackytechtom — Fri, 13 May 2022 20:35:04 GMT

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/

Re: How to get an aggregated value for each week

yve214 — Wed, 18 May 2022 03:25:43 GMT

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

Re: How to get an aggregated value for each week

Icey — Wed, 18 May 2022 08:28:13 GMT

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.

Re: How to get an aggregated value for each week

yve214 — Thu, 19 May 2022 17:13:00 GMT

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