Forum Discussion

yve214's avatar
yve214
Icon for Helper III rankHelper III
4 years ago
Solved

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 M...
DAX Calculation
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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