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 the Week To Date Calculation:

Week to Date Reqs = 
var CurrentDate=LASTDATE(Calendar_DIM[Date])
var DayNumberOfWeek=WEEKDAY(LASTDATE(Calendar_DIM[Date]),1)
return
CALCULATE(
    [Requirements],
DATESBETWEEN(
    Calendar_DIM[Date],
DATEADD(
    CurrentDate,
    -1*DayNumberOfWeek,
    DAY),
    CurrentDate))

 

I appreciate your help in advance!

 

Thank you,
Lance M 

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.

5 Replies

  • 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.