topic Table with all dates in an interval in DAX Commands and Tips

Table with all dates in an interval

Anonymous — Wed, 26 Jan 2022 19:20:20 GMT

I have a table with this example data:

Solicitation	IDAsset	Open_at	Closed_at
REQ0001	BKN0001	2022/01/20	2022/01/25

I need to generate a table with date range:

Solicitation	IDAsset	Date
REQ0001	BKN0001	2022/01/20
REQ0001	BKN0001	2022/01/21
REQ0001	BKN0001	2022/01/22
REQ0001	BKN0001	2022/01/23
REQ0001	BKN0001	2022/01/24
REQ0001	BKN0001	2022/01/25

Help?

Re: Table with all dates in an interval

Greg_Deckler — Wed, 26 Jan 2022 19:26:28 GMT

Anonymous You can use GENERATE and CALENDAR like:

Table1 = VAR __Table = 'Table' VAR __Table1 = GENERATE(__Table, CALENDAR([Open_at], [Closed_at])) RETURN __Table1

Re: Table with all dates in an interval

ValtteriN — Wed, 26 Jan 2022 19:31:31 GMT

Hi,

You can do this in PQ like this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7PyUzOLEksyczPU9JR8nRxLC5OLQGy/AtS8+ITQSznnPzi1BQQO1YnWinINdDAwMAQKO7k7QdlGRkYGekbGOobGSBzTJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Solicitation", type text}, {"IDAsset", type text}, {"Open_at", type date}, {"Closed_at", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "NewDate", each {Number.From([Open_at])..Number.From([Closed_at])}),
#"Expanded NewDate" = Table.ExpandListColumn(#"Added Custom", "NewDate"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded NewDate",{{"NewDate", type date}})
in
#"Changed Type2"

The bolded steps are what you want. Also I recommend cheking out this video from Curbal explaining the logic behind this:
https://www.youtube.com/watch?v=u3HLseZxSj4

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Table with all dates in an interval

Jihwan_Kim — Thu, 27 Jan 2022 05:03:19 GMT

Hi,

Please check the below picture and the attached pbix file.

New Table =
VAR _calendartable =
SELECTCOLUMNS ( CALENDARAUTO (), "@date", [Date] )
VAR _newtable =
GENERATE (
Data,
FILTER (
_calendartable,
VAR _mindate =
SELECTEDVALUE ( Data[Open_at] )
VAR _maxdate =
SELECTEDVALUE ( Data[Closed_at] )
RETURN
[@date] >= _mindate
&& [@date] <= _maxdate
)
)
RETURN
SUMMARIZE ( _newtable, Data[Solicitation], Data[IDAsset], [@date] )

Re: Table with all dates in an interval

Anonymous — Thu, 27 Jan 2022 12:15:16 GMT

Jihwan_Kim good morning! Thank you for your answer!

The soluction works well for a little data, I couldn't make it work with a big data (30M rows, for example).

Re: Table with all dates in an interval

Anonymous — Thu, 27 Jan 2022 12:17:49 GMT

Good morning ValtteriN! Thank you so much for your answer, I can resolve my problem with your example!!!

My code:

let
Source = Table.SelectRows(maint_reqs, each ([status] <> "Cancelada" and [status] <> "Reprovado" and [Categoria] = "EQUIPAMENTOS")),
#"Added Custom0" = Table.AddColumn(Source, "executed_at_solved", each if[executed_at] is null then DateTime.LocalNow() else [executed_at]),
#"Select Columns" = Table.SelectColumns(#"Added Custom0", {"reqNum-SS", "BKNativo", "opened_at", "executed_at_solved"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Select Columns", {{"reqNum-SS", type text}, {"BKNativo", type text}, {"opened_at", type date}, {"executed_at_solved", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each {Number.From([opened_at])..Number.From([executed_at_solved])}),
#"Expanded NewDate" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded NewDate",{{"Date", type date}})
in
#"Changed Type2"