Find value within a category for last date & put it in a new column for the current date

[BenediktV]

Hello Power BI Community 😊,

I’m not sure how to describe the problem i have and i also couldn’t find a similar solved problem.

So, I will show a screenshot with the sample data & the desired result of my problem.

The only thing i need is what is under "desired result" (Date_Inbetr_last_Month).

I hope the screenshot is self-explaining and the problem description is not needed 😅

Sampledata:

Problem desciption:

Basically, what I’m doing is doing measurements every month for all out contracts.

The blue column is the date of the measurement.

The column "Date_Inbetr" shows the starting date for the contract.

Sometimes this starting date changes.

If that happens, we want to know for what extend.

That should then be shown in the yellow column.

This sample data is sorted, so it's easy to understand.

Thanks for the help in advance 😊

Benedikt.

@amitchandak @Greg_Deckler @Jihwan_Kim @PaulDBrown @Ashish_Mathur @johnt75 @lbendlin @rsbin @ryan_mayu @DataInsights 

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Kundenauft"}, {{"All", each _, type table [Kundenauft=nullable number, Datum Datei=nullable datetime, Dat_Inbetr=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
DateList = Table.RemoveFirstN([All],1)[Dat_Inbetr]&{null},AllLists = Table.ToColumns([All]),
Colheaders = Table.ColumnNames([All]) & {"Next date"},
Finaltable = AllLists & {DateList}
in
Table.FromColumns(Finaltable,Colheaders)),
    Custom1 = Table.Combine(#"Added Custom"[Custom]),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Kundenauft", type text}, {"Datum Datei", type datetime}, {"Dat_Inbetr", type datetime}, {"Next date", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Diff", each [Dat_Inbetr]-[Next date])
in
    #"Added Custom1"

Hope this helps.

[BenediktV · Accepted solution]

I managed to find the solution with a DAX Measure to calculate the Column "Date_Inbetr_last_Month".

Date_Inbetr_last_Month =

VAR CurrentKundenauft = 'Fact_ZWAM'[Kundenauft]

VAR CurrentDate = 'Fact_ZWAM'[Datum Datei]

VAR PreviousMonth_ =

    EOMONTH ( CurrentDate, -1 )

VAR PreviousMonthInbetr =

    CALCULATE (

        MAX ( 'Fact_ZWAM'[Dat_Inbetr] ),

        FILTER (

            'Fact_ZWAM',

            'Fact_ZWAM'[Kundenauft] = CurrentKundenauft

                && 'Fact_ZWAM'[Datum Datei] = PreviousMonth_

        )

    )

VAR MaxPreviousDate =

    CALCULATE (

        MAX ( 'Fact_ZWAM'[Datum Datei] ),

        FILTER (

            'Fact_ZWAM',

            'Fact_ZWAM'[Kundenauft] = CurrentKundenauft

                && 'Fact_ZWAM'[Datum Datei] <= PreviousMonth_

                && NOT ISBLANK ( 'Fact_ZWAM'[Dat_Inbetr] )

        )

    )

RETURN

    IF (

        ISBLANK ( PreviousMonthInbetr )

            && NOT ISBLANK ( MaxPreviousDate ),

        CALCULATE (

            MAX ( 'Fact_ZWAM'[Dat_Inbetr] ),

            FILTER (

                'Fact_ZWAM',

                'Fact_ZWAM'[Kundenauft] = CurrentKundenauft

                    && 'Fact_ZWAM'[Datum Datei] = MaxPreviousDate

            )

        ),

        PreviousMonthInbetr

    )

[Ashish_Mathur]

Hi,

In simple language, in your desired result column you want the date from the next row (of the Dat_Inbter column).  is my understanding correct? 

[BenediktV]

Hello @Ashish_Mathur ,

Thank you for your reply.

It looks like this in the sample data

BUT

The sample data is sorted.

• 1st sort: Kundenauft
• 2nd sord: Datum Datei

So if the data is not sorted, it’s not the next row.

If the data is sorted like in my example, then it is the next row within a contract.

In this case your understanding would be correct.

The last value would be a Blank!!! for each contract.

But I think you understand it correct.

It is basically something like a parallel period -1, but within 2 conditions (Kundenauft, Datum Datei).

[Ashish_Mathur]

You are welcome.  Share data in a format that can be pasted in an MS Excel file.

[BenediktV]

Hello @Ashish_Mathur ,

i uploaded a Datasample for.

I hope this works for you.

Kundenauft	Datum Datei	Dat_Inbetr	Desired Result	Final Result
600787	07.11.2022	16.05.2022	16.05.2022	0
600787	31.10.2022	16.05.2022	16.05.2022	0
600787	30.09.2022	16.05.2022	16.05.2022	0
600787	31.08.2022	16.05.2022	16.05.2022	0
600787	29.07.2022	16.05.2022	16.05.2022	0
600787	30.06.2022	16.05.2022	16.05.2022	0
600787	31.05.2022	16.05.2022	16.05.2022	0
600787	29.04.2022	16.05.2022	16.05.2022	0
600787	31.03.2022	16.05.2022	16.05.2022	0
600787	28.02.2022	16.05.2022	16.05.2022	0
600787	31.01.2022	16.05.2022	16.05.2022	0
600787	30.12.2021	16.05.2022	16.05.2022	0
600787	30.11.2021	16.05.2022	15.12.2021	152
600787	29.10.2021	15.12.2021	15.12.2021	0
600787	30.09.2021	15.12.2021	15.12.2021	0
600787	31.08.2021	15.12.2021	15.12.2021	0
600787	30.07.2021	15.12.2021	15.12.2021	0
600787	30.06.2021	15.12.2021	15.12.2021	0
600787	31.05.2021	15.12.2021	15.11.2020	395
600787	30.04.2021	15.11.2020	15.11.2020	0
600787	31.03.2021	15.11.2020	15.11.2020	0
600787	26.02.2021	15.11.2020	15.11.2020	0
600787	29.01.2021	15.11.2020	15.11.2020	0
600787	30.12.2020	15.11.2020	15.11.2020	0
600787	30.11.2020	15.11.2020	15.11.2020	0
600787	30.10.2020	15.11.2020	15.11.2020	0
600787	30.09.2020	15.11.2020	15.11.2020	0
600787	31.08.2020	15.11.2020	15.11.2020	0
600787	31.07.2020	15.11.2020	15.11.2020	0
600787	30.06.2020	15.11.2020	15.11.2020	0
600787	29.05.2020	15.11.2020	15.11.2020	0
600787	30.04.2020	15.11.2020	15.11.2020	0
600787	31.03.2020	15.11.2020	15.11.2020	0
600787	28.02.2020	15.11.2020
600721	31.07.2020	24.12.2020	24.12.2020	0
600721	30.06.2020	24.12.2020	24.12.2020	0
600721	29.05.2020	24.12.2020	11.11.2020	43
600721	30.04.2020	11.11.2020	11.11.2020	0
600721	31.03.2020	11.11.2020	11.11.2020	0
600721	28.02.2020	11.11.2020
600844	31.07.2020	24.12.2020	24.12.2020	0
600844	30.06.2020	24.12.2020	24.12.2020	0
600844	29.05.2020	24.12.2020	11.11.2020	43
600844	30.04.2020	11.11.2020	11.11.2020	0
600844	31.03.2020	11.11.2020	11.11.2020	0
600844	28.02.2020	11.11.2020
700303	30.06.2020	30.11.2020	23.05.2019	557
700303	29.05.2020	23.05.2019	23.05.2019	0
700303	30.04.2020	23.05.2019	23.05.2019	0
700303	31.03.2020	23.05.2019	23.05.2019	0
700303	28.02.2020	23.05.2019

I also added the desired result & final result column, but all i need is a DAX-Code (maybe with a bit explanation), that ll give me the desired result column.

The Sampledata is sorted aswell, in the actuall data it could look more like this.

Thank you for trying to help me 🙂

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Kundenauft"}, {{"All", each _, type table [Kundenauft=nullable number, Datum Datei=nullable datetime, Dat_Inbetr=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
DateList = Table.RemoveFirstN([All],1)[Dat_Inbetr]&{null},AllLists = Table.ToColumns([All]),
Colheaders = Table.ColumnNames([All]) & {"Next date"},
Finaltable = AllLists & {DateList}
in
Table.FromColumns(Finaltable,Colheaders)),
    Custom1 = Table.Combine(#"Added Custom"[Custom]),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"Kundenauft", type text}, {"Datum Datei", type datetime}, {"Dat_Inbetr", type datetime}, {"Next date", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Diff", each [Dat_Inbetr]-[Next date])
in
    #"Added Custom1"

Hope this helps.

[BenediktV]

Hi @Ashish_Mathur ,

long time no see.

I had some time trying to solve the problem again and it worked with a DAX Measure.

Regardless i ll accept your solution aswell since it seems to work, just not for me for some reason.

Best regards

Benedikt

[BenediktV]

Hey @Ashish_Mathur ,

thank you for your solution.

I was more looking for a solution with DAX, but I’m fine with a solution in Power Query as well.

Obviously, you showed that this works, but unfortunately this code doesn't work in my existing Power Query & I don't understand M Code.

It probably doesn't work, because I already have steps and don't start with a blank query, and I don't use a single excel worksheet but a whole folder with excel worksheets instead.

I can read very easy M Code, but mostly I use buttons in Power Query and if I create a new column I use "Column From Examples".

So maybe you could show me step by step what you clicked, when you created each step.

E.g., when you created "Grouped Rows", could you show where you clicked. (I know this is for columns, not for rows, but i have no idea how you did that ... 😅)

Something like this, but for the other steps as well.

Then I could understand how you dealt with this problem and could use it for other similar problems as well.

I hope solving this problem is even possible with clicks instead of using raw M-Code.

Sorry for the inconvenience, guess i have to improve alot on M-Code in the future.

[Ashish_Mathur]

Hi,

one has to write M code to solve this problem.  One cannot get this result by clicking on buttons in the ribbon.

[BenediktV]

Hi @Ashish_Mathur ,

i understand what you mean.

I thought maybe you could give me the code step by step if that's possible.

So i could identify where the error comes from.

I'm not sure if that works though.

Or maybe i get a code where i just have to enter my last step in my code, something like this:

Currently it looks like this, and i tried to modify your code, without success.

Regardless, i have to put more time into this, but currently i have alot of work to do.

I ll try to look into it later and try to modify your code so that it ll work and mark your post as solution if it helped me to solve it (it probably is right and i feel kinda bad that i'm not good enough yet to implement it into my report).

Regardless, i appreciate your help and the code you sent me 😊

[BenediktV]

Or maybe a code without the step "Source" works, since I already have all my excel sheets in power query, but I would prefer a step-by-step solution, so I could use it for similar problems as well.