Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dax for Current Week & Previous Week

Hi, My requirement got changed like below. I have a Date slicer and my week starting from Monday to Sunday. If user selects Today (May 24,2021) then i should be able to see the data only for Curre...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You could add a variable to calculate the most recent monday (relative to the MAX date), and then calculate your measure between the max date and that Monday date.

     

    Weekly =
    VAR _selectedDate =
        MAX ( Table1[DayDate] )
    VAR _endOfWeekDate =
        _selectedDate + 7
            - WEEKDAY ( _selectedDate, 2 )
    VAR lastMonday =
        CALCULATE (
            MAX ( Table1[DayDate] ),
            FILTER (
                ALL ( Table1[DayDate] ),
                Table1[DayDate] <= _selecteddate
                    && WEEKDAY ( Table1[DayDate] ) = 2
            )
        )
    RETURN
        CALCULATE (
            SUM ( Table1[TH] ),
            FILTER (
                ALL ( Table1[DayDate] ),
                Table1[DayDate] <= _endOfWeekDate
                    && Table1[DayDate] >= lastMonday
            )
        )

     

    Pat

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

Not sure why that didn't work.  Do you have other date related columns in the visual?  In any case, another approach to consider is to add a DAX column with the week ending date and use that in your slicer.

 

WeekEnding = Table1[DayDate] + 8 - WEEKDAY(Table1[DayDate])

 

Pat

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

mahoneypat Yes its there and the column name is weekdate in the line & Clustered Column chart visual. and also Weekdate is filtered as TOP 12 Latest Weekdate.

DayDate is my Slicer.

 

Pat, If you can see the attached screenshot yesterday, iam not using any dates apart from DayDate in my Testing Table Visual and even iam not able to get the data as per my requirement.

 

Thanks,

Venky

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    mahoneypat Hi Pat,

    Apologies not to apply proper filter.

    I had a discussion with my client last week and your code was working as expected after applying Weekdate filter to get top 12 latest dates.

    For Previous/Completed week (05/31/2021 to 06/06/2021) data is fine and only problem with current week. They wanted to have the data for available dates only like if data is available for June 7 & 8th and users selects 06/10/2021 and then only  then take those 2days sum and divide by 2. not to take sum of 4 days and divide by 4days. Basically with the above code summarization is fine and problem with to take only available dates data

    With your above code iam using to create another same measure and only change is Sum(Quantity) instead of Sum(TH) and using these two measures to get percentage

    Weekly_Qty =
    VAR _selectedDate =
        MAX ( Table1[DayDate] )
    VAR _endOfWeekDate =
        _selectedDate + 7
            - WEEKDAY ( _selectedDate, 2 )
    VAR lastMonday =
        CALCULATE (
            MAX ( Table1[DayDate] ),
            FILTER (
                ALL ( Table1[DayDate] ),
                Table1[DayDate] <= _selecteddate
                    && WEEKDAY ( Table1[DayDate] ) = 2
            )
        )
    RETURN
        CALCULATE (
            SUM ( Table1[QTY] ),
            FILTER (
                ALL ( Table1[DayDate] ),
                Table1[DayDate] <= _endOfWeekDate
                    && Table1[DayDate] >= lastMonday
            )
        )

     

    Percentage = 

    DIVIDE(DIVIDE([Weekly],1440,0),[Weekly_QTY],0)
     
    Note: With the [Weekly_QTY] variable iam getting 7 (days) which i wanted to change based on selected date and avalable data for the dates
     
    I have changed below variable but iam getting from the selected date is 4 which is nor correct.
    Weekly_Qty =
    VAR _selectedDate =
        MAX ( Table1[DayDate] )
    VAR _endOfWeekDate =
        _selectedDate + 7
            - WEEKDAY ( _selectedDate, 2 )
    VAR lastMonday =
        CALCULATE (
            MAX ( Table1[DayDate] ),
            FILTER (
                ALL ( Table1[DayDate] ),
                Table1[DayDate] <= _selecteddate
                    && WEEKDAY ( Table1[DayDate] ) = 2
            )
        )
    RETURN
        CALCULATE (
            SUM ( Table1[QTY] ),
            FILTER (
                ALL ( Table1[DayDate] ),
                //Table1[DayDate] <= _endOfWeekDate
                 Table1[DayDate] <= _selectedDate
                    && Table1[DayDate] >= lastMonday
            )
        )
     
     
     
    Thanks,
    Venky