Forum Discussion

DHB's avatar
DHB
Icon for Helper V rankHelper V
2 years ago
Solved

DAX to Calculate Sum from Previous Week

Hi all,   I wonder if anyone can point me in the right direction with this.  I'm trying to get a value for the previous week (selected date-7) according to the selected date.   Now this gives me...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Try this aproach

    1. Create a Calendar Table with calculated column formulas of Year, Month name and Month number.  Sort the Month name column by the Month number
    2. Create a relationship (Many to One and Single) from the Date column of your Fact table to the Date column of the Calendar Table
    3. To your visua/slicer/filter, drag Date from the Calendar Table
    4. Write these measures

    Total = SUM('F - Actual and Projected EFTSL'[Actual EFTSL])

    Total in previous week = calculate([Total],datesbetween(calendar[date],min(calendar[date])-7,min(calendar[date)))

    Hope this helps.

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi DHB ,

    Based on my testing, please try the following methods:

    1.Create the sample table.

    2.Create the measure to calculate the previous week.

     

    PreviousWeekEFTSL = 
CALCULATE(
    SUM('Table'[Values]),
    DATESBETWEEN('Table'[Date], MIN('Table'[Date]) - 7, MIN('Table'[Date]))
    //DATEADD('Table'[Date], -7, DAY)
)

     

    3.Drag the date column into the slicer visual.

    4.Select the date. The result is shown below.

     

    DATESBETWEEN function (DAX) - DAX | Microsoft Learn

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

What about something like:

Prior Week Version = VAR priorDate = SELECTEDVALUE('F - Actual and Projected EFTSL'[Snapshot Date]) - 7
VAR output = CALCULATE(
    SUM('F - Actual and Projected EFTSL'[Actual EFTSL]),
    ALL('F - Actual and Projected EFTSL'[Snapshot Date]),
    'F - Actual and Projected EFTSL'[Snapshot Date] = priorDate
)
RETURN
output
DHB's avatar
DHB
Icon for Helper V rankHelper V
2 years ago

Thank you Anonymous.  Now that query doesn't error but it returns a blank in my table for some reason.

If I create a test measure just using this I do get the right date though:

SELECTEDVALUE('F - Actual and Projected EFTSL'[Snapshot Date]) - 7