Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX-How to get end date in a register when end date is not in the table but the line item is removed

Hi All,

 

Below is the table which has product with Risk date but it has no End date, the line item will be taken out from Risk Category with Weekly file upload as in column File Number & File Date. The end date should be the File Number Date when it was removed. Product Blue & Diamond are the 2 product with 2 cases scenario for below example. If the Product again come back in rsk category the result table should have a new line item with the new Risk start date & new risk end date in future.

 

Data Table

File Number

File Number Date

Product

Risk Date

 

W1

1/01/2017

Orange

1/01/2015

 

W1

1/01/2017

Red

1/01/2015

 

W1

1/01/2017

Blue 

1/01/2015

 

W1

1/01/2017

violet

1/01/2015

 

W1

1/01/2017

GEM

1/01/2015

 

W1

1/01/2017

Gold

1/01/2015

 

W1

1/01/2017

Diamond

1/01/2015

 

W1

1/01/2017

Solar

1/01/2015

 

W1

1/01/2017

Coffee

1/01/2015

 

W2

8/01/2017

Orange

1/01/2015

 

W2

8/01/2017

Red

1/01/2015

 

W2

8/01/2017

Blue 

1/01/2015

 

W2

8/01/2017

violet

1/01/2015

 

W2

8/01/2017

GEM

1/01/2015

 

W2

8/01/2017

Gold

1/01/2015

 

W2

8/01/2017

Solar

1/01/2015

 

W2

8/01/2017

Coffee

1/01/2015

 

W3

15/01/2017

Orange

1/01/2015

 

W3

15/01/2017

Red

1/01/2015

 

W3

15/01/2017

violet

1/01/2015

 

W3

15/01/2017

GEM

1/01/2015

 

W3

15/01/2017

Gold

1/01/2015

 

W3

15/01/2017

Solar

1/01/2015

 

W3

15/01/2017

Coffee

1/01/2015

 

W4

22/01/2017

Orange

1/01/2015

 

W4

22/01/2017

Red

1/01/2015

 

W4

22/01/2017

violet

1/01/2015

 

W4

22/01/2017

GEM

1/01/2015

 

W4

22/01/2017

Gold

1/01/2015

 

W4

22/01/2017

Solar

1/01/2015

 

W4

22/01/2017

Coffee

1/01/2015

 

W5

29/01/2017

Orange

1/01/2015

 

W5

29/01/2017

Red

1/01/2015

 

W5

29/01/2017

violet

1/01/2015

 

W5

29/01/2017

GEM

1/01/2015

 

W5

29/01/2017

Gold

1/01/2015

 

W5

29/01/2017

Solar

1/01/2015

 

W5

29/01/2017

Coffee

1/01/2015

 

W6

5/02/2017

Orange

1/01/2015

 

W6

5/02/2017

Red

1/01/2015

 

W6

5/02/2017

Blue 

4/07/2015

Risk Identified again

W6

5/02/2017

violet

1/01/2015

 

W6

5/02/2017

GEM

1/01/2015

 

W6

5/02/2017

Gold

1/01/2015

 

W6

5/02/2017

Diamond

5/07/2015

Risk Identified again

W6

5/02/2017

Solar

1/01/2015

 

W6

5/02/2017

Coffee

1/01/2015

 

 

 

 

Result Table

   

Product

Date Start

End Date= File Number Date

Comment

Orange

1/01/2017

 

On going

Red

1/01/2017

 

On going

Blue 

1/01/2017

15/01/2017

14 days in risk

violet

1/01/2017

 

On going

GEM

1/01/2017

 

On going

Gold

1/01/2017

 

On going

Diamond

1/01/2017

 8/01/2017

7 days in risk

Solar

1/01/2017

 

On going

Coffee

1/01/2017

 

On going

Blue 

4/07/2017

 

On going

Daimond

5/07/2017

 

On going

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Looks like you only need the File Number Date and the Product from your data table.

Data Table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddNNCsIwEIbhu2RdSBMoTrb+4EoEXZYuCqZSqA0U9TyexZO5kLTfJNPtuwgPH5O6VkYbbUuzUYU6T+1496opWL34W5q2w8t/P2l992Hwz7QeD6cshSF7ct+3jzBm+RqGdkrjLnSd/ztJ1FOuJ1FPop5yPQl6kpgkMk2lS3Fm7PPQGHFU7POsLM7DYoUNMQPPWpnHeuSxCDzWI4/HyGN14bGMPLfCcxLPrfCcxHMiz8k8J/MqbQUc1EiDBDcIFbhQIxZTpELDHwR58UOM+uYH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Number Date" = _t, Product = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"File Number Date", type date}}, "en-GB")
in
    #"Changed Type with Locale"

 

You don't provide any baseline so the dimension tables for the cross join need to be inferred.

Products:

let
    Source = Table.Distinct(Table.SelectColumns(#"Data Table",{"Product"}))
in
    Source

Dates:

let
    Source = Table.Distinct(Table.SelectColumns(#"Data Table",{"File Number Date"}))
in
    Source

 

Then you can load these three tables into your data model.

lbendlin_0-1672785468829.png

Using a matrix visual you can emulate the crossjoin, and you can visualize the gaps for Blue and Diamond.

lbendlin_1-1672785621641.png

From here you can decide if you really need the aggregated view or if the graphical representation is enough.

 

You can also explore GroupType Local in Power Query.

 

See attached.

 

 

 

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

Looks like you only need the File Number Date and the Product from your data table.

Data Table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddNNCsIwEIbhu2RdSBMoTrb+4EoEXZYuCqZSqA0U9TyexZO5kLTfJNPtuwgPH5O6VkYbbUuzUYU6T+1496opWL34W5q2w8t/P2l992Hwz7QeD6cshSF7ct+3jzBm+RqGdkrjLnSd/ztJ1FOuJ1FPop5yPQl6kpgkMk2lS3Fm7PPQGHFU7POsLM7DYoUNMQPPWpnHeuSxCDzWI4/HyGN14bGMPLfCcxLPrfCcxHMiz8k8J/MqbQUc1EiDBDcIFbhQIxZTpELDHwR58UOM+uYH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Number Date" = _t, Product = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"File Number Date", type date}}, "en-GB")
in
    #"Changed Type with Locale"

 

You don't provide any baseline so the dimension tables for the cross join need to be inferred.

Products:

let
    Source = Table.Distinct(Table.SelectColumns(#"Data Table",{"Product"}))
in
    Source

Dates:

let
    Source = Table.Distinct(Table.SelectColumns(#"Data Table",{"File Number Date"}))
in
    Source

 

Then you can load these three tables into your data model.

lbendlin_0-1672785468829.png

Using a matrix visual you can emulate the crossjoin, and you can visualize the gaps for Blue and Diamond.

lbendlin_1-1672785621641.png

From here you can decide if you really need the aggregated view or if the graphical representation is enough.

 

You can also explore GroupType Local in Power Query.

 

See attached.

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors