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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors