DAX to Calculate Sum from Previous Week

[DHB]

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.

[Ashish_Mathur · Accepted solution]

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.

[v-jiewu-msft · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[RossEdwards]

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]

Thank you @RossEdwards.  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