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

[Anonymous]

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

[lbendlin · Accepted solution]

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.

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

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.