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
olimilo
Responsive Resident
Responsive Resident

Get the most recent available value based on context (quarter period)

I've been looking around for solutions both here and /r/PowerBI but I don't think this has been touched on before. With the given dataset below, is it possible to display the most recent available grade based on the selected period? The Period filter is based on a Calendar table, so it's possible to select a Period that doesn't have any data in it or has incomplete data (eg: Q4 2018 onwards).

 

NamePeriodRatingTATReportsEvaluationDate
Name AQ1 20181=Poor353/1/2018
Name AQ2 20183=Average436/1/2018
Name AQ3 20182=Below Average269/1/2018
Name AQ4 2018 2412/1/2018
Name BQ1 20183=Average343/1/2018
Name BQ3 20182=Below Average249/1/2018
Name BQ4 2018 5712/1/2018
Name CQ1 20183=Average173/1/2018
Name CQ2 20183=Average566/1/2018
Name DQ2 20184=Above Average336/1/2018
Name DQ3 20183=Average529/1/2018
Name EQ1 20182=Below Average233/1/2018


Case 1:

If no periods are selected, display the most recent available Rating. Since the Calendar includes dates in the future (Q4 2018), when you select Q4 2018, it should still display the most recent available Rating. In this case:

Name A = 2 (Q3), Name B = 2 (Q3), Name C = 3 (Q2), Name D = 3 (Q3), Name E = 2 (Q1)

 

Case 2:

If one period is selected, display only the Ratings for that period. If Q2 is selected, only the following will be displayed:

Name A = 3, Name C = 3, Name D = 4

 

Case 3:

If more than one period is selected, only the most recent available Rating should be displayed. If Q2 and Q3 are selected:

Name A = 2 (Q3), Name B = 2 (Q3), Name C = 3 (Q2), Name D = 3 (Q3)

 

I tried the solution from this thread and it only solves the first case. The code below however results in a "EARLIER/EARLIEST refers to an earlier row context which doesn't exist." error.

 

~Most Recent Grade = 
    CALCULATE(
        MAX('Grade'[Grade]),
        FILTER(
            ALL('Grade'),
            'Grade'[EvaluationDate] = EARLIER('Grade'[EvaluationDate], 1)
        )
    )

Anyone know how to get around this?

1 REPLY 1
v-cherch-msft
Employee
Employee

Hi @olimilo

 

You may refer to below measure to get measure for case1. Then you may use IF condition to get the different case measures.

RecentDate =
CALCULATE (
    MAX ( Grade[EvaluationDate] ),
    FILTER ( ALLEXCEPT ( Grade, Grade[Name] ), Grade[EvaluationDate] <= NOW () )
)
MostRecentRating1 =
CALCULATE (
    MAX ( Grade[Rating] ),
    FILTER (
        ALLEXCEPT ( Grade, Grade[Name] ),
        Grade[EvaluationDate] = [RecentDate]
    )
)

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.