Problem with pulling latest value from a table based on date filter.

[MichaelBauld]

Hi there,

I'm building a corporate scorecard and am running into an issue where a certain DAX measure which pulls the latest KPI score for my Conservation Performance Indicator (CPI) works perfectly, but behaves completely differently when I use the same approach on a near identical table holding the CPI Targets. 

CPI Actuals

Valid From	Valid To	CPI Actuals
01/03/2020	28/02/2021	0.653
01/03/2021	28/02/2022	0.669
01/03/2022	28/02/2023	0.679

CPI Targets

CPI Target	Valid From	Valid To
0.65	01/03/2020	28/02/2021
0.67	01/03/2021	28/02/2022
0.68	01/03/2022	28/02/2023
0.70	01/03/2023	29/02/2024
0.72	01/03/2024	28/02/2025
0.74	01/03/2025	28/02/2026
0.76	01/03/2026	28/02/2027
0.78	01/03/2027	29/02/2028
0.80	01/03/2028	28/02/2029
0.82	01/03/2029	28/02/2030
0.84	01/03/2030	28/02/2031
0.86	01/03/2031	29/02/2032
0.88	01/03/2032	28/02/2033

Both fact tables are related to a calendar table via a many to one single sided relationship. 

To get the current KPI based on the selected date on my dashboard I am using the following DAX measure:

CPI Actuals = 
VAR _LastDtInRange = MAX( 'Calendar'[Date] )
VAR _AllCurrentAndPreviousKPIs = CALCULATETABLE( 'CPI Annual Scores', 'Calendar'[Date] <= _LastDtInRange )
VAR _LatestKPIrow = TOPN( 1, _AllCurrentAndPreviousKPIs , 'CPI Annual Scores'[Valid from] , DESC )
VAR _LatestKPIval = LASTNONBLANK( CALCULATETABLE( VALUES( 'CPI Annual Scores'[CPI] ), _LatestKPIrow ), 1 )
RETURN
_LatestKPIval

To get the same results but for the CPI target I simply reworked the above DAX to refer to the CPI Target fact table:

CPI Target = 
VAR _LastDtInRange = MAX( 'Calendar'[Date] )
VAR _AllCurrentAndPreviousKPIs = CALCULATETABLE( 'CPI Targets', 'Calendar'[Date] <= _LastDtInRange )
VAR _LatestKPIrow = TOPN( 1, _AllCurrentAndPreviousKPIs , 'CPI Targets'[Valid From] , DESC )
VAR _LatestKPIval = LASTNONBLANK( CALCULATETABLE( VALUES( 'CPI Targets'[CPI Target] ), _LatestKPIrow ), 1 )
RETURN
_LatestKPIval

In my mind this should be straightforward and deliver the same result for both the Actuals and Targets, but when I display the outputs in a matrix the actuals display correctly, but the target data just pulls through the last value in the table (0.88) for every date in the calendar table.

YearMonth Text	CPI Target	CPI Actuals
	88.00%
2019 Mar	88.00%
2019 Apr	88.00%
2019 May	88.00%
2019 Jun	88.00%
2019 Jul	88.00%
2019 Aug	88.00%
2019 Sep	88.00%
2019 Oct	88.00%
2019 Nov	88.00%
2019 Dec	88.00%
2020 Jan	88.00%
2020 Feb	88.00%
2020 Mar	88.00%	65.30%
2020 Apr	88.00%	65.30%
2020 May	88.00%	65.30%
2020 Jun	88.00%	65.30%
2020 Jul	88.00%	65.30%
2020 Aug	88.00%	65.30%
2020 Sep	88.00%	65.30%
2020 Oct	88.00%	65.30%
2020 Nov	88.00%	65.30%
2020 Dec	88.00%	65.30%
2021 Jan	88.00%	65.30%
2021 Feb	88.00%	65.30%
2021 Mar	88.00%	66.90%
2021 Apr	88.00%	66.90%
2021 May	88.00%	66.90%
2021 Jun	88.00%	66.90%
2021 Jul	88.00%	66.90%
2021 Aug	88.00%	66.90%
2021 Sep	88.00%	66.90%
2021 Oct	88.00%	66.90%
2021 Nov	88.00%	66.90%
2021 Dec	88.00%	66.90%
2022 Jan	88.00%	66.90%
2022 Feb	88.00%	66.90%
2022 Mar	88.00%	67.90%
2022 Apr	88.00%	67.90%
2022 May	88.00%	67.90%
2022 Jun	88.00%	67.90%
2022 Jul	88.00%	67.90%
2022 Aug	88.00%	67.90%

I've checked the relationship to the calendar table and it looks correct as well as checking that the 'valid from' columns are correctly formatted as dates (they are). Any other suggestions as to what might be going on here would be greatly appreciated as I can't seem to figure it out! 

Thanks in advance for any help you can give.

Kind regards

Michael

[MichaelBauld]

Thanks @onurbmiguel_ , that suggestion seems to work!

Cheers

Michael

[onurbmiguel_ · Accepted solution]

Hello Michael 

Please take a look at this CPI.pbix 

https://1drv.ms/u/s!AkcWVrMFkXs1h7Req7Ff1DU5YZAloQ?e=w1e46L

Any question please ask 

Best regards

Bruno Costa | Continued Contributor

Did I help you to answer your question? Accepted my post as a solution! Appreciate your Kudos!! 👍

Take a look at the blog: PBI Portugal 

[MichaelBauld]

Hi @onurbmiguel_ ,

I've been working with your solution but have noticed that it is returning only values for the 'Valid From' month, not all months between the valid from and valid to dates. This is fine when the user is just defaulting to all dates in the current year, but when they select a specified month (say July 2022) then the values returned are 'Blank'.

YearMonth Text	% CPI Actuals	% CPI Targets
2022 Mar	67.90%	68.00%
2022 Apr
2022 May
2022 Jun
2022 Jul
2022 Aug

% CPI Targets = 
 var _max = MAX('calendar'[Date])

 var _value = 
    CALCULATE(
        AVERAGE('CPI Targets'[CPI Target]),
        'CPI Targets'[Valid From]<=_max,
        'CPI Targets'[Valid To]>=_max
    )
return 
_value
 

Do you have any thoughts on why this is?

Cheers

Michael