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

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Pat,

I tried to breakdown this formula but seems like not getting what iam expecting. Could you please help me to find exact one

 05/27/2021 is my Max Date and also it falls in Current Week (my week:Monday to Sunday) so iam expecting the result is sum of (05/24/2021+05/25/2021+05/26/2021+05/27/2021)

if user select the any date in completed week (Completed week 05/17/2021 to 05/23/2021) then i should be able to get sum of whole completed week

Ex: if i select 05/23/2021 then sum of 05/17/2021 to 05/23/2021

 

and also iam i tried below logic but not working

Z_Selected Dte =
Var _SDate = SELECTEDVALUE(Table1[DayDate])
Var _EndDate = _SDate-WEEKDAY(_SDate,2)
Return
CALCULATE(
SUM(Table1[TH]),
FILTER(ALL(Table1[DayDate]),
[z_Max_DayDate]<=_EndDate
)
)

 

Regards,

Venky

 

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