Tag a record

[CJ_96601]

Can someone help me on how to tag a record in power bi query using filter.

Ex:

count records if order date <= calendar and delivery date is > calendar - tag the record late delivery and so on

thanks

[v-jiascu-msft]

Hi @CJ_96601 ,

If you'd like to show them in a report, please try out this measure. 

test =
IF (
    MIN ( 'sales'[order date] ) <= TODAY ()
        && MIN ( 'sales'[delivery date] ) > TODAY (),
    "Late Delivery",
    "Normal"
)

Best Regards,

[CJ_96601]

Hi, thanks.

Instead of today, i need to have a variable date, in filter.

Regards, 

Obet

[v-jiascu-msft]

Hi Obet,

You can try this one in that case.

test =
VAR selectedDate =
    IF (
        ISBLANK ( SELECTEDVALUE ( 'Calendar'[Date] ) ),
        TODAY (),
        SELECTEDVALUE ( 'Calendar'[Date] )
    )
RETURN
    IF (
        MIN ( 'sales'[order date] ) <= selectedDate
            && MIN ( 'sales'[delivery date] ) > selectedDate,
        "Late Delivery",
        "Normal"
    )

Best Regards,

[CJ_96601]

Thanks.  can i use power query or measure?

[v-jiascu-msft]

Hi @CJ_96601 ,

Could you please mark the proper answers as solutions?

Best Regards,

[CJ_96601]

Hi, thanks, but it is not the solution i am looking for.

I need the tag in additional column, in power query

regards, 

[v-jiascu-msft · Accepted solution]

Hi @CJ_96601 ,

There is a problem. If you' like a column in Power Query, you have to assign the date to use. In other words, you can't make it dynamic. Since you need a column, there could be two solutions.

1. A calculated column with DAX.

test =
IF (
     'sales'[order date]  <= TODAY ()
        && 'sales'[delivery date] > TODAY (),
    "Late Delivery",
    "Normal"
)

2. A new column with Power Query.

if [order date] <= DateTime.LocalNow() and [delivery Date] > DateTime.LocalNow() 
then "Late Delivery" 
else "Normal"

Best Regards,

[CJ_96601]

Thanks.

[v-jiascu-msft]

My pleasure. It's a measure.

Best Regards,