Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
yve214
Helper III
Helper III

Dax Help

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)

 

CategoryDateQtyExpected result

a

1/3/2022100-
a

1/4/2022

150-
a

1/5/2022

50-
a

1/6/2022

300-
a1/7/2022100-
a1/8/202225-
a1/9/20220725
a1/10/2022101-
a1/11/202285-
a1/12/2022100-
a1/13/2022150 
a1/14/2022100-
a1/15/202255-
a1/16/20220591

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Untitled.png

 

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, "-" )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

2 REPLIES 2
yve214
Helper III
Helper III

@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.

Jihwan_Kim
Super User
Super User

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.

 

Untitled.png

 

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, "-" )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.