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