Forum Discussion

abbas_uddin's avatar
abbas_uddin
Icon for Helper I rankHelper I
6 years ago
Solved

Max Date or last based on specific column

Hi Experts,

 

Since i am new to Power BI, need help on the subject. The scenario is i am pulling a data set and what i want to achieve is the max or last of date based on specific column.

 

i have customers and their sub category by invoice date. i want the list of customer by applying max / last of invoice date regardless of what category it was, it should show me the last row or max row. see the example below:

 

Raw Data:

CompanyCustomerCustomer CategoryCustomer Category 2Invoice Date
XYYAB-1RetailWS COMBO01-Feb-19
XYYAB-1Domestic othersWS COMBO26-Feb-19
XYYDC-2RetailKA- SUPERMARKET13-Jan-19
XYYDC-2Domestic othersKA- SUPERMARKET

31-Jan-19

 

Final result should be below:

 

CompanyCustomerCustomer CategoryCustomer Category 2Invoice Date
XYYAB-1Domestic othersWS COMBO26-Feb-19
XYYDC-2Domestic othersKA- SUPERMARKET31-Jan-19

 

So the last date of customer AB-1 and DC-2 is 26 Feb and 31 Jan respectively. They had different category regardless of that it should show the last invoice date happened on customer level not the category level.

 

kindly let me know how it can be achieved, even if it creates a new table from existing table also im fine with it.

 

Regards

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi abbas_uddin ,

     

    You need to calculate the max date and compare dates with it. Then use a flag ( a measure )to filter your data.

    Measure =
VAR a =
    CALCULATE (
        MAX ( 'Table'[Invoice Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer] )
    )
RETURN
    IF ( a = SELECTEDVALUE ( 'Table'[Invoice Date] ), 1, 0 )

    Now you could set the rule in the filter pane to show data whose measure is 1.

    Here is my test file for your reference.

     

2 Replies