Need Help, state on the first day of year

[Patrick_PowerBI]

I have an table where an item can have a different status over time. Now I like to have the status on the first day of the year.

ID	startdate	enddate	EPA label
1	2015-10-01 00:00:00.000	2016-05-12 00:00:00.000	D
1	2016-05-13 00:00:00.000	2016-06-30 00:00:00.000	D
1	2016-07-01 00:00:00.000	2017-03-14 00:00:00.000	D
1	2017-03-15 00:00:00.000	2017-06-30 00:00:00.000	D
1	2017-07-01 00:00:00.000	2018-02-12 00:00:00.000	D
1	2018-02-13 00:00:00.000	2018-02-13 00:00:00.000
1	2018-02-14 00:00:00.000	2018-06-30 00:00:00.000	D
1	2018-07-01 00:00:00.000	2018-07-01 00:00:00.000	D
1	2018-07-01 00:00:00.000	2018-12-10 00:00:00.000	D
1	2018-12-11 00:00:00.000	2018-12-11 00:00:00.000	D
1	2018-12-11 00:00:00.000	2018-12-11 00:00:00.000	D
1	2018-12-11 00:00:00.000	2019-06-30 00:00:00.000	D
1	2019-07-01 00:00:00.000	2019-07-01 00:00:00.000	D
1	2019-07-01 00:00:00.000	2019-10-13 00:00:00.000	D
1	2019-10-14 00:00:00.000	2020-06-30 00:00:00.000	D
1	2020-07-01 00:00:00.000	2020-07-01 00:00:00.000	D
1	2020-07-01 00:00:00.000	2020-07-01 00:00:00.000	D
1	2020-07-01 00:00:00.000	9999-12-31 00:00:00.000	D
2	2015-10-01 00:00:00.000	2016-05-12 00:00:00.000	D
2	2016-05-13 00:00:00.000	2016-06-30 00:00:00.000	D
2	2016-07-01 00:00:00.000	2017-03-14 00:00:00.000	D
2	2017-03-15 00:00:00.000	2017-06-30 00:00:00.000	E
2	2017-07-01 00:00:00.000	2018-02-12 00:00:00.000	E
2	2018-02-13 00:00:00.000	2018-06-30 00:00:00.000	E
2	2018-07-01 00:00:00.000	2018-07-01 00:00:00.000	E
2	2018-07-01 00:00:00.000	2019-06-30 00:00:00.000	E
2	2019-07-01 00:00:00.000	2019-07-01 00:00:00.000	E
2	2019-07-01 00:00:00.000	2020-06-30 00:00:00.000	E
2	2020-07-01 00:00:00.000	2020-07-01 00:00:00.000	E

In T-SQL I would do something like:

SELECT
ID,
startdate,
enddate,
[EPA label]
FROM [table]
WHERE '2016-01-01' between startdate and enddate
union
SELECT
ID,
startdate,
enddate,
[EPA label]
FROM [table]
WHERE '2017-01-01' between startdate and enddate
ORDER BY
ID,
startdate
etc.

To get:

ID	startdate	enddate	EPA label
1	2015-10-01 00:00:00.000	2016-05-12 00:00:00.000	D
1	2016-07-01 00:00:00.000	2017-03-14 00:00:00.000	C
2	2015-10-01 00:00:00.000	2016-05-12 00:00:00.000	D
2	2016-07-01 00:00:00.000	2017-03-14 00:00:00.000	A
3	2015-10-01 00:00:00.000	2016-05-12 00:00:00.000	D
3	2016-07-01 00:00:00.000	2017-03-14 00:00:00.000	D
4	2015-10-01 00:00:00.000	2016-05-12 00:00:00.000	D
4	2016-07-01 00:00:00.000	2017-03-14 00:00:00.000	A
5	2015-10-01 00:00:00.000	2016-05-12 00:00:00.000	D
5	2016-07-01 00:00:00.000	2017-03-14 00:00:00.000	E

How do I do this in Power-BI in either M or Dax?

I can use what I made and make it in M but are there better ways?

thanks!

[AlB · Accepted solution]

Hi @Patrick_PowerBI 

You can do it in M or DAX. Here is an M option. Place the following M code in a blank query to see the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZTdDoMgDIVfxXBtl1Lm3663pzC+/2sMRrJNaCloNF55+uW0YM+6Gmt6Q2gHsAhoO8TH570hYlRGQC9SqjzN1n/pWON4egSHGj3x3v6zA3sv0rFm4GnVexK9Z0DS5o413NyC0uV0Nl1U1M7nUuecUk1b35XmHWpkWvO+hl4qTm0R5xaUatrvT37fCR1qmPsm1DsPNby3pFxPL/4Jt+EEmk5lC53Klh99JFvoYLa8ErotW3Z0MVtU7+Z0qKSFHdvRzTtWR0tb8k+3/+ee3t4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, startdate = _t, enddate = _t, #"EPA label" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"startdate", type datetime}, {"enddate", type datetime}, {"EPA label", type text}}),


    years_ = List.Distinct(List.Transform(List.Combine({#"Changed Type"[startdate], #"Changed Type"[enddate]} ), each Date.Year(_))),
    firstDays_ = List.Transform(years_, each DateTime.From(Text.From(_) & "-01-01")), 
    res_ = Table.SelectRows(#"Changed Type", each List.AnyTrue(List.Transform(firstDays_, (inner)=> [startdate] <= inner and inner<=[enddate]))) 
in
    res_

 The 3 last steps are the important ones

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Patrick_PowerBI]

Thanks this is a very very nice solution 👍