CODEWITHSUNDEEP
powerbidax
Andrew A
Andrew A

Reputation: 13

Using Lastdate with if

First of all I'm still new to Power bi and Dax so please bear with me on this. Thank you

I want to add a new calculated column called Product Type and this will be based on per location.

On the sample table that I have below

product code aaa123 has 3 records with 3 different movement dates and 3 different locations.

I want to get the most recent movement date for product aaa123, then if the location is NSW then product A, if WA then product B

any suggestions?

Product Code Movement Date To Location
aaa123 12 Nov 2022 NSW
aaa123 31 Oct 2022 ACT
aaa123 15 Nov 2022 WA
bbb123 10 Nov 2022 NSW
bbb123 14 Nov 2022 NSW
bbb123 01 Nov 2022 WA
ccc123 31 Oct 2022 WA
ccc123 01 Nov 2022 VIC
ccc123 02 Nov 2022 QLD

I was thinking of using the LASTDATE and IF DAX functions but I don't know how to proceed. Any suggestions?

Upvotes: 1

Views: 105

Answers (2)

Jos Woolley
Jos Woolley

Reputation: 9062

If you want a solution using DAX alone:

=
VAR ThisProductCode = Table1[Product Code]
VAR LatestDate =
    CALCULATE(
        MAX( Table1[Movement Date] ),
        FILTER(
            Table1,
            Table1[Product Code] = ThisProductCode
        )
    )
VAR LatestLocation =
    CALCULATE(
        MAX( Table1[To Location] ),
        FILTER(
            Table1,
            Table1[Product Code] = ThisProductCode
                && Table1[Movement Date] = LatestDate
        )
    )
RETURN
    SWITCH(
        LatestLocation,
        "NSW", "Product A",
        "WA", "Product B"
    )

Upvotes: 1

AmilaMGunawardana
AmilaMGunawardana

Reputation: 1830

Assuming that this is an imported dataset, we can achieve this by using two steps.

1st Step ( Group by the data )

Go to Transform data -> select the table -> Transform -> Group By

Transform Data

Then do the group by as follows (Make sure you have correct data types for columns)

Group By

This will give you a table as follows.

Group by table

Now expand the table and select the columns as shown in the Screenshot.

Expand table

Select columns

Then add a conditional column by selecting add Column->Conditional column and add condition as per the Screenshot

Conditional Column

Please remove the Latest date column and change the "Is Latest" column data type to whole number

Final table

Then close and apply, then in the front select the table and add a New column

Front end

Product = 
IF (
    'sample data'[Is Latest] = 1,
    IF (
        'sample data'[To Location] = "NSW",
        "Product A",
        IF ( 'sample data'[To Location] = "WA", "Product B", BLANK () )
    ),
    BLANK ()
)

Table will look like below.

Intended result

Please upvote and accept my answer, cheers!!!

Upvotes: 1

Related Questions