Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to Solution.
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, "-" )
)
@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.
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, "-" )
)
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |