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, get a free DP-600 exam voucher to use by the end of 2024. Register 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

6 REPLIES 6
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/

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.

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


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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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