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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NeilL
Frequent Visitor

Need Help With DAX Filtering

Hello All,
I am new to DAX and am stuck.  I don't quite understand the FILTER() function which is, I think, the reason I am stuck.

 

I have two tables, Dates & Forecast, which are joined on a field labeled Date.  Dates table is a series of days from 1/1/2019 thru 12/31/2050.  Forecast table is a series of Project Titles, Dates, Revenues & Earnings, there are 75 projects with multiple rows containing dates and numbers, enough to fill up 23k rows.  For simplicity I'm only looking at the first project.

 

I finally was able to create a running total using quick measures and now have exactly what I want except that the running total continues running trough the end of the Dates table (12/31/2050) date when it should stop at 12/31/2019 (the last month for the project 452).

 

This first image shows the earliest date for my project 452 - begins with 6/1/2019.

Capture0.PNG

 

The next image shows the last date for the same project 452 - ends on 12/31/2019. The next row 

Capture1.PNG

 

In my report I see the running total doing exactly what I want/need it to do thru 12/2019, however, it doesn't stop. 

 

I would like for the running total to stop with the last month for that project then the next project appear in the list and see its Revenue and running total... rinse and repeat.

Capture2.PNG

 

For reference, below is the formula for the running total, as produced by the quick measure:

 

Capture3.PNG

I really appreciate any and all help!

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@NeilL -

You could modify the calculation like the following:

 

Running Total = 
var maxForecastDateInContext = MAX(Forecast[Date])
var lastDateInContext = MAX('Date'[Date])
var lastRelevantDate = MIN(lastDateInContext, maxSalesDate)
return CALCULATE(
    SUM('Forecast'[Revenue]),
    'Date'[Date] <= lastRelevantDate
)

Hope this Helps,

Nathan

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@NeilL -

You could modify the calculation like the following:

 

Running Total = 
var maxForecastDateInContext = MAX(Forecast[Date])
var lastDateInContext = MAX('Date'[Date])
var lastRelevantDate = MIN(lastDateInContext, maxSalesDate)
return CALCULATE(
    SUM('Forecast'[Revenue]),
    'Date'[Date] <= lastRelevantDate
)

Hope this Helps,

Nathan

@Anonymous That solved my problem!  Thank you for the speedy reply and helping me solve my issue!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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