Forum Discussion

MichaelBauld's avatar
MichaelBauld
Frequent Visitor
3 years ago
Solved

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

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 perfec...
Need Help
MichaelBauld's avatar
MichaelBauld
Frequent Visitor
3 years ago

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 Mar67.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