topic Re: Dax Help in DAX Commands and Tips

Dax Help

yve214 — Wed, 18 May 2022 06:59:41 GMT

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)

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: Dax Help

Jihwan_Kim — Wed, 18 May 2022 07:15:25 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new measure.

In my opinion, one of the best ways to solve the issue is having a DIM-Date table and connect it with the fact table.

However, the below is created without having a dim date table.

Expected result measure: = VAR currentweeknumberISO = WEEKNUM ( MAX ( Data[Date] ), 21 ) VAR currentyearISO = IF ( MONTH ( SELECTEDVALUE ( Data[Date] ) ) = 1 && currentweeknumberISO > 51, YEAR ( MAX ( Data[Date] ) ) - 1, YEAR ( MAX ( Data[Date] ) ) ) VAR weekqtysum = CALCULATE ( SUM ( Data[Qty] ), FILTER ( ALL ( Data ), Data[Category] = MAX ( Data[Category] ) && WEEKNUM ( Data[Date], 21 ) = currentweeknumberISO && IF ( MONTH ( Data[Date] ) = 1 && currentweeknumberISO > 51, YEAR ( Data[Date] ) - 1, YEAR ( Data[Date] ) ) = currentyearISO ) ) VAR lastdateoftheweek = MAXX ( FILTER ( ALL ( Data ), Data[Category] = MAX ( Data[Category] ) && WEEKNUM ( Data[Date], 21 ) = currentweeknumberISO && IF ( MONTH ( Data[Date] ) = 1 && currentweeknumberISO > 51, YEAR ( Data[Date] ) - 1, YEAR ( Data[Date] ) ) = currentyearISO ), Data[Date] ) RETURN IF ( HASONEVALUE ( Data[Date] ), IF ( MAX ( Data[Date] ) = lastdateoftheweek, weekqtysum, "-" ) )

Re: Dax Help

yve214 — Thu, 19 May 2022 17:38:35 GMT

Jihwan_Kim Thank you so much for your help. Your query is quite detailed I actually understood the process of thinking through a solution like this.