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 the value for the selected date:

CALCULATE(SUM('F - Actual and Projected EFTSL'[Actual EFTSL]),'F - Actual and Projected EFTSL'[Snapshot Date]=SELECTEDVALUE('F - Actual and Projected EFTSL'[Snapshot Date]))
 
I thought I could just take it back 7 days like this:
CALCULATE(SUM('F - Actual and Projected EFTSL'[Actual EFTSL]),'F - Actual and Projected EFTSL'[Snapshot Date]=SELECTEDVALUE('F - Actual and Projected EFTSL'[Snapshot Date]),-7,DAYS)
 
However PBI is telling me that,
"The True/false expression does not specify a column.  Each True/False expression used as a table filter expression must refer to exactly one column."
 
What do I need to do to the DAX to make it work?
 
Thanks in advance.
 
Best,
 
DHB.
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.

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    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.

    • Hello_masters's avatar
      Hello_masters
      Frequent Visitor
      1 year ago

      How do I apply this logic with W-1. E.g. I want to sum the total qty which falls in W-1. My table consist of product name, qty, date.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        1 year ago

        Share some data, explain the question and show the expected result.

  • 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
  • Anonymous's avatar
    Anonymous
    Not applicable
    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.