Forum Discussion

newgirl's avatar
newgirl
Post Patron
7 years ago
Solved

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

 

 

 

 

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

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

2 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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,