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
MichaelBauld
Frequent Visitor

Find the last recorded value based on filter context

Hi there,

 

This is probably a very simple problem, but for some reason I am really struggling. I have a table with 3 rows which represent annual KPI measures:

 

Date                  KPI

01/03/2019      0.65

01/03/2020      0.67

01/03/2021      0.68

 

I'm building a scorecard view with a range of different measures and tables and for all of them I want to pull into the report the latest values based on the date filter context  (drawn from my calendar table). For the above KPI this works fine when I am simply using an annual filter, but as soon as I look at quarters or months (which will be necessary) the value returned to the card is blank as the only month with data is March. 

 

What I am struggling with is how to build a DAX measure which will return the last value within the selected date range regardless of whether the specified month/quarter actually has a recorded value. i.e.

 

March 2020 = 0.67

July 2020 = 0.67

March 2021 = 0.68 

...and so on

 

Can anyone suggest how I might go about this?

 

Kind regards

Michael

1 ACCEPTED SOLUTION

The basic approach I think you want is to 1) get latest date for time period in your visual, 2) get all KPIs from latest date from #1 and earlier, 3) get the most recent KPI in subset from #2.

Here is a measure that follows this approach:

 

Latest KPI = 
VAR _LastDtInRange = MAX( 'Calendar'[Date] )
VAR _AllCurrentAndPreviousKPIs = CALCULATETABLE( KPIs, 'Calendar'[Date] <= _LastDtInRange )
VAR _LatestKPIrow = TOPN( 1, _AllCurrentAndPreviousKPIs , KPIs[Date] , DESC )
VAR _LatestKPIval = LASTNONBLANK( CALCULATETABLE( VALUES( KPIs[KPI] ), _LatestKPIrow ), 1 )
RETURN
_LatestKPIval

 

Output (note it works in year, month, quarter, etc. filter context):

MarkLaf_1-1661444508228.png

FYI here is the model I set up for this to work. Note that I'm using same sample data you provided in initial post:

MarkLaf_2-1661444588321.png

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Please ensure that you have a Calendar Table with calculated columns for Year, Month name and Month number.  Sort the Month name by the Month number.  There should be a relationship (Many to One and Single) between the Data Table and the Calendar Table.  To your slicers/filters, drag Year and Month name from the Calendar Table and select any one Year and Month name.  Write this measure

= CALCULATE([KPI],LASTNONBLANK('Calendar'[Date],CALCULATE([KPI])))

Hope this helps.

 


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

@MichaelBauld Maybe:

Measure =
  VAR __StartDate = MIN('Calendar'[Date])
  VAR __EndDate = MAX('Calendar'[Date])
  VAR __MaxDate = MAXX(FILTER('Table',[Date] >= __StartDate && [Date]<=__EndDate),[Date])
RETURN
  MAXX(FILTER('Table',[Date] = __MaxDate),[KPI])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thanks @Greg_Deckler , this seems to work when I use a date slider, but when I have a separate year and month drop down it still delivers blank results when the month selected isn't one that has a value within the KPI table:

 

Scorecard ScreenshotScorecard Screenshot

 

Would I need to make the value of the KPI a cumulative sum based on the filter of year and month so it delivers a value in all months?

 

Thanks!

Michael

@MichaelBauld Well, you use ALL or ALLSELECTED like this:

Measure =
  VAR __StartDate = MIN('Calendar'[Date])
  VAR __EndDate = MAX('Calendar'[Date])
  VAR __MaxDate = MAXX(FILTER(ALL('Table'),[Date] >= __StartDate && [Date]<=__EndDate),[Date])
RETURN
  MAXX(FILTER('Table',[Date] = __MaxDate),[KPI])

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler , thanks for the suggestion. I tried using the ALL function as you suggested but I was still getting the same problem of blank values on all months except March (3). Probably doing somethign wrong on my end though!

The basic approach I think you want is to 1) get latest date for time period in your visual, 2) get all KPIs from latest date from #1 and earlier, 3) get the most recent KPI in subset from #2.

Here is a measure that follows this approach:

 

Latest KPI = 
VAR _LastDtInRange = MAX( 'Calendar'[Date] )
VAR _AllCurrentAndPreviousKPIs = CALCULATETABLE( KPIs, 'Calendar'[Date] <= _LastDtInRange )
VAR _LatestKPIrow = TOPN( 1, _AllCurrentAndPreviousKPIs , KPIs[Date] , DESC )
VAR _LatestKPIval = LASTNONBLANK( CALCULATETABLE( VALUES( KPIs[KPI] ), _LatestKPIrow ), 1 )
RETURN
_LatestKPIval

 

Output (note it works in year, month, quarter, etc. filter context):

MarkLaf_1-1661444508228.png

FYI here is the model I set up for this to work. Note that I'm using same sample data you provided in initial post:

MarkLaf_2-1661444588321.png

Thanks @MarkLaf , your approach works perfectly. This will really help with some other tables I will need to pull in to the scorecard as well which follow a simialr pattern of variable date inputs (typically annually or quarterly). Thanks again!

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.