Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DHB
Helper V
Helper V

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.
2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

v-jiewu-msft
Community Support
Community Support

Hi @DHB ,

Based on my testing, please try the following methods:

1.Create the sample table.

vjiewumsft_0-1722246073002.png

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.

vjiewumsft_1-1722246103965.png

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

vjiewumsft_2-1722246131534.png

 

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.

View solution in original post

4 REPLIES 4
v-jiewu-msft
Community Support
Community Support

Hi @DHB ,

Based on my testing, please try the following methods:

1.Create the sample table.

vjiewumsft_0-1722246073002.png

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.

vjiewumsft_1-1722246103965.png

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

vjiewumsft_2-1722246131534.png

 

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
RossEdwards
Solution Sage
Solution Sage

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.