Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
BenediktV
Regular Visitor

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

‎11-08-2022 07:45 AM

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:

 

 

IBN Challange.png

 

 

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 

Labels:
Message 1 of 12
468 Views
0
2 ACCEPTED SOLUTIONS
Ashish_Mathur
Super User
Super User
In response to BenediktV

‎11-11-2022 07:42 PM

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.

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Message 6 of 12
368 Views
0
BenediktV
Regular Visitor

‎08-30-2023 04:56 AM
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
    )

View solution in original post

Message 12 of 12
234 Views
0
11 REPLIES 11
BenediktV
Regular Visitor

‎08-30-2023 04:56 AM
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
    )
Message 12 of 12
235 Views
0
Ashish_Mathur
Super User
Super User

‎11-08-2022 03:55 PM

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? 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 2 of 12
412 Views
0
BenediktV
Regular Visitor
In response to Ashish_Mathur

‎11-09-2022 04:34 AM

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).

 

 

IBN Challange 2.png

Message 3 of 12
396 Views
0
Ashish_Mathur
Super User
Super User
In response to BenediktV

‎11-09-2022 06:46 PM

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 4 of 12
381 Views
0
BenediktV
Regular Visitor
In response to Ashish_Mathur

‎11-11-2022 01:15 AM

Hello @Ashish_Mathur ,

 

i uploaded a Datasample for.

I hope this works for you.

 

KundenauftDatum DateiDat_InbetrDesired ResultFinal Result
60078707.11.202216.05.202216.05.20220
60078731.10.202216.05.202216.05.20220
60078730.09.202216.05.202216.05.20220
60078731.08.202216.05.202216.05.20220
60078729.07.202216.05.202216.05.20220
60078730.06.202216.05.202216.05.20220
60078731.05.202216.05.202216.05.20220
60078729.04.202216.05.202216.05.20220
60078731.03.202216.05.202216.05.20220
60078728.02.202216.05.202216.05.20220
60078731.01.202216.05.202216.05.20220
60078730.12.202116.05.202216.05.20220
60078730.11.202116.05.202215.12.2021152
60078729.10.202115.12.202115.12.20210
60078730.09.202115.12.202115.12.20210
60078731.08.202115.12.202115.12.20210
60078730.07.202115.12.202115.12.20210
60078730.06.202115.12.202115.12.20210
60078731.05.202115.12.202115.11.2020395
60078730.04.202115.11.202015.11.20200
60078731.03.202115.11.202015.11.20200
60078726.02.202115.11.202015.11.20200
60078729.01.202115.11.202015.11.20200
60078730.12.202015.11.202015.11.20200
60078730.11.202015.11.202015.11.20200
60078730.10.202015.11.202015.11.20200
60078730.09.202015.11.202015.11.20200
60078731.08.202015.11.202015.11.20200
60078731.07.202015.11.202015.11.20200
60078730.06.202015.11.202015.11.20200
60078729.05.202015.11.202015.11.20200
60078730.04.202015.11.202015.11.20200
60078731.03.202015.11.202015.11.20200
60078728.02.202015.11.2020  
60072131.07.202024.12.202024.12.20200
60072130.06.202024.12.202024.12.20200
60072129.05.202024.12.202011.11.202043
60072130.04.202011.11.202011.11.20200
60072131.03.202011.11.202011.11.20200
60072128.02.202011.11.2020  
60084431.07.202024.12.202024.12.20200
60084430.06.202024.12.202024.12.20200
60084429.05.202024.12.202011.11.202043
60084430.04.202011.11.202011.11.20200
60084431.03.202011.11.202011.11.20200
60084428.02.202011.11.2020  
70030330.06.202030.11.202023.05.2019557
70030329.05.202023.05.201923.05.20190
70030330.04.202023.05.201923.05.20190
70030331.03.202023.05.201923.05.20190
70030328.02.202023.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.

 

IBN Challange 3.png

 

Thank you for trying to help me 🙂

Message 5 of 12
372 Views
0
Ashish_Mathur
Super User
Super User
In response to BenediktV

‎11-11-2022 07:42 PM

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.

Untitled.pngUntitled1.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 6 of 12
369 Views
0
BenediktV
Regular Visitor
In response to Ashish_Mathur

‎08-30-2023 05:00 AM

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

Message 11 of 12
228 Views
0
BenediktV
Regular Visitor
In response to Ashish_Mathur

‎11-14-2022 06:16 AM

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.

 

IBN Challange 4.png

 

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.

 

IBN Challange 5.png

 

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.

Message 7 of 12
349 Views
0
Ashish_Mathur
Super User
Super User
In response to BenediktV

‎11-14-2022 06:37 PM

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 9 of 12
340 Views
0
BenediktV
Regular Visitor
In response to Ashish_Mathur

‎11-15-2022 08:28 AM

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:

 

IBN Challange 6.png

 

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

 

IBN Challange 7.png

 

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 😊

Message 10 of 12
330 Views
0
BenediktV
Regular Visitor
In response to BenediktV

‎11-14-2022 06:21 AM

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.

Message 8 of 12
346 Views
0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

View All