cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newgirl
Helper IV
Helper IV

Recent Value

Hi!

 

I am relatively new in using Power BI. In the current file I'm working on, I am facing some challenges.

 

Here are my sample tables (refer to first 2 screen shots)

 

Main Table - combined of monthly data

DateClientVolumeKPIDelivered from
5-DecA50PassLocation X
18-DecB20PassLocation Y
23-DecA30FailLocation X
3-FebB10FailLocation Z
14-FebC20PassLocation X
27-FebC30PassLocation Z
8-MarA20FailLocation Y
19-MarB20FailLocation Y
26-MarC30PassLocation Z

 

Category Table - combined data based on quarterly survey results. However, it's not required for the client to answer the quarterly survey.

DateClientCategory
Mar 2019APromoter
Mar 2019BDetractor
Dec 2018ADetractor
Dec 2018CPromoter
Sept 2018CPromoter
Sept 2018BPromoter

 

I should have an output like below but illustrated in 3 different scenarios  (depending on the month chosen by the reader).

Table to be reportedTable to be reported

 

The category column should display the most recent category of the client, depending on the month chosen by the reader. Below illustrates on how the report should look like in connection with the tables.

 

If as of Mar 2019.JPG

 

If as of Feb 2019.JPG

 

If as of Dec 2018.JPG

 

I have 2 main challenges:

  • What do you suggest on how I would connect the Main table and the Category table, considering that the Category table can be repeating Client? I am thinking that I should duplicate the raw data from the Category Table and change their dates into months that are not shown in the Category Table (i.e. February, January, November, etc...). and then add additional column wherein I merged the Date and Client - this would be my reference value. Then I would create a relationship with the Main table by  adding a new column wherein I merged the Client (from Main Table) and Date (from Main Table) as well. However, you guys might have an easier way of creating the relationship for the 2 tables.
  • Next challenge is how do I get the most recent value? I have seen some forum questions asking for the recent value but their values are number data types. What I have is Text.

 

I hope you guys could help me out!

 

 

THank you

DK

1 ACCEPTED SOLUTION
v-cherch-msft
Microsoft
Microsoft

Hi @newgirl 

You may try below measure.Attached sample file for your reference.

Measure = 
VAR recent_date =
    CALCULATE (
        MAX ( Category[MonthYear] ),
        FILTER (
            ALL ( Category ),
            Category[Client] = MAX ( Main[Client] )
                && Category[MonthYear] <= MAX ( 'Date'[MonthYear] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Category[Category] ),
        FILTER (
            Category,
            Category[Client] = MAX ( Main[Client] )
                && Category[MonthYear] = recent_date
        )
    )

Regards,

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.

View solution in original post

2 REPLIES 2
v-cherch-msft
Microsoft
Microsoft

Hi @newgirl 

You may try below measure.Attached sample file for your reference.

Measure = 
VAR recent_date =
    CALCULATE (
        MAX ( Category[MonthYear] ),
        FILTER (
            ALL ( Category ),
            Category[Client] = MAX ( Main[Client] )
                && Category[MonthYear] <= MAX ( 'Date'[MonthYear] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Category[Category] ),
        FILTER (
            Category,
            Category[Client] = MAX ( Main[Client] )
                && Category[MonthYear] = recent_date
        )
    )

Regards,

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.

View solution in original post

Thank you so much @v-cherch-msft !!! This worked perfectly. You are heaven-sent! 

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors