compare the values of one column, based on the date from another column

[Vibration85]

Hi everyone,

If anyone can help me, how to compare the values of one column, based on the date from another column? I have a column that is included IDs (I have 2 or several IDs per day), I need to find the IDs from each day and put a label for each ID category in another column.

in the below example: I have Run ID which for Oct 15th, I have 4 rows with 2 codes, I wanted to put the label in another column, 60 as the first running, 61 the second running. 

thanks

[v-alq-msft · Accepted solution]

Hi, @Vibration85 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column as below.

Column = 
var tab =
SUMMARIZE(
    ALL('Table'),
    'Table'[Run ID],
    'Table'[Date]
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Rank",
    var re = 
    COUNTROWS(
        FILTER(
            tab,
            [Date]=EARLIER('Table'[Date])&&
            [Run ID]<EARLIER('Table'[Run ID])
        )
    )+1
    return re&" Running"
)
return
MAXX(
    FILTER(
        newtab,
        [Run ID]=EARLIER('Table'[Run ID])&&
        [Date]=EARLIER('Table'[Date])
    ),
    [Rank]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Vibration85]

Awesome Allan @v-alq-msft 

Many thanks, it was great and exactly what I was looking for. So clean and perfect. 😊 

Best regards

[shaowu459]

Hi, @Vibration85 

Check if this is what you need:

let
    Source = Excel.CurrentWorkbook(){[Name="Table16"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{"Run_Datetime", type date}),
    Custom1 = Table.Combine(Table.Group(ChangeType,"Run_Datetime",{"n",each Table.AddColumn(_,"Num",(x)=>Text.From(1+List.PositionOf(List.Distinct([Run ID]),x[Run ID]))&" Running")})[n])
in
    Custom1

 

[Vibration85]

dear @shaowu459 

could you give me the DAX code, please?

Thank you so much 🙂 

[shaowu459]

Sorry, I didn't notice you need DAX code😂 I'm not familiar with DAX, so let's wait for DAX expert to help you.

[Vibration85]

Thank you so much for your effort 😁

by the way, as my time is so limited, could you give me a hint on how to use this M language? 😉

I'm not familiar with M lang at all, a dummy on it.

[shaowu459]

FYI.

[Vibration85]

Hey dude, apart from everything, I put my efforts and learn how to use it and it did work, many thanks 🙂

[Vibration85]

Thanks Pal, @shaowu459 

It was not working for me, 😁 as I had many changes before this modification in the advance editor and I think a little bit different in code structure with I saw in Power Query advance editor structure. anyway, thanks a lot. Hope anyone can help me with the DAX code. 

Cheers