Forum Discussion

KevinMann's avatar
KevinMann
New Member
1 year ago

Latest KPI value weekly result using last KPI set date

Having trouble trying to show a table with latest KPI vaule on a week by week basis, to then be using in a line graph.

The Latest KPI is to show what the new KPI is that has been set to be used on a weekly basis.

 

I have tried a few measures but can't seem to get it right.  

Below is the closest I have managed to get to, however the Latest KPI results are including the previous value, where I only want the result to show the new value (latest KPI) going forwards.

 

Any ideas, where I may be going wrong.

Many Thanks  

 

 

"DAX" "Need help" "Weekly KPI"

2 Replies

  • KevinMann's avatar
    KevinMann
    New Member
    1 year ago

    Sorry forgot to add [KPI Date] is the date that the KPI has been set/updated.

    Relationship set as below, if this helps

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi KevinMann ,

     

    Modify your formula like below:

    LatestKPI = 
CALCULATE(
    MAX('Table'[KPIValue]),
    FILTER(
        'Table',
        'Table'[Date] = MAX('Table'[Date])
    )
)
    WeeklyLatestKPI = 
CALCULATE(
    [LatestKPI],
    ALLEXCEPT('Table', 'Table'[Week])
)

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.