Max Date or last based on specific column

[abbas_uddin]

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:

Company	Customer	Customer Category	Customer Category 2	Invoice Date
XYY	AB-1	Retail	WS COMBO	01-Feb-19
XYY	AB-1	Domestic others	WS COMBO	26-Feb-19
XYY	DC-2	Retail	KA- SUPERMARKET	13-Jan-19
XYY	DC-2	Domestic others	KA- SUPERMARKET	31-Jan-19

Final result should be below:

Company	Customer	Customer Category	Customer Category 2	Invoice Date
XYY	AB-1	Domestic others	WS COMBO	26-Feb-19
XYY	DC-2	Domestic others	KA- SUPERMARKET	31-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

[v-eachen-msft · Accepted solution]

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.

[amitchandak]

Refer, if this can help

https://community.powerbi.com/t5/Desktop/highest-value-by-category/td-p/428758

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601