Forum Discussion

sirlanceohlott's avatar
sirlanceohlott
Advocate III
6 years ago
Solved

Previous Week To Date DAX

Good afternoon,   I hope you are all doing well and staying healthy.   I have a Week To Date Calculation and I'd like to create a Previous Week To Date Calculation; however, I'm lost.   Here's ...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi, sirlanceohlott 

    Try measures as below:

    Last Week to Date Reqs 2 = 
VAR CurrentDate =
    LASTDATE ( Calendar_DIM[Date] )
VAR DayNumberOfWeek =
    WEEKDAY ( LASTDATE ( Calendar_DIM[Date] ), 1 )
RETURN
    CALCULATE (
        [Requirements],
        FILTER (
            ALL ( Calendar_DIM[Date] ),
            WEEKDAY ( [Date], 1 ) <= DayNumberOfWeek
                && [Date] <= CurrentDate - DayNumberOfWeek &&   [Date] >= CurrentDate - DayNumberOfWeek -7
        )
    )

     

    Pbix attached

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
6 years ago

Try Like

Week to Date Reqs = 
var _curr= LASTDATE(Calendar_DIM[Date])
var _min= _curr + (-1*WEEKDAY(_curr)+1) -7
var _max =_curr-7

return
CALCULATE(
    [Requirements],
filter(Calendar_DIM,Calendar_DIM[Date]>=_min && Calendar_DIM[Date]<=_max))

/////////////OR

Week to Date Reqs = 
var _curr= LASTDATE(Calendar_DIM[Date])
var _min= _curr + (-1*WEEKDAY(_curr)+1) -7
var _max =_curr-7

return
CALCULATE(
    [Requirements],
filter(all(Calendar_DIM),Calendar_DIM[Date]>=_min && Calendar_DIM[Date]<=_max))
  • sirlanceohlott's avatar
    sirlanceohlott
    Advocate III
    6 years ago

    amitchandak,

     

    I appreciate you reaching out and sharing the DAX you did.

     

    When I run the following: 

    PREV Week to Date Reqs = 
var _curr= LASTDATE(Calendar_DIM[Date])
var _min= _curr + (-1*WEEKDAY(_curr)+1) -7
var _max =_curr-7

return
CALCULATE(
    [Requirements],
filter(all(Calendar_DIM),Calendar_DIM[Date]>=_min && Calendar_DIM[Date]<=_max))

     

    It shows the entire week's worth instead of the previous week to date(Sun-Wed). 

    • v-easonf-msft's avatar
      v-easonf-msft
      Community Support
      6 years ago

      Hi, sirlanceohlott 

      Try measures as below:

      Last Week to Date Reqs 2 = 
VAR CurrentDate =
    LASTDATE ( Calendar_DIM[Date] )
VAR DayNumberOfWeek =
    WEEKDAY ( LASTDATE ( Calendar_DIM[Date] ), 1 )
RETURN
    CALCULATE (
        [Requirements],
        FILTER (
            ALL ( Calendar_DIM[Date] ),
            WEEKDAY ( [Date], 1 ) <= DayNumberOfWeek
                && [Date] <= CurrentDate - DayNumberOfWeek &&   [Date] >= CurrentDate - DayNumberOfWeek -7
        )
    )

       

      Pbix attached

       

      Best Regards,
      Community Support Team _ Eason
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.