Forum Discussion
Consolidating Dates in a Report Title.
Hi IMarvinTPA
DAX can compress vertical sequences, but it's not super optimised for it.
This is pretty close, but you can see it's not a trivial peice of code.
But this measure.
Date Measure = VAR A = VALUES('DD'[Day Date]) VAR B = CALENDAR(MIN('DD'[Day Date]),MAX('DD'[Day Date])) VAR Gaps = SELECTCOLUMNS(EXCEPT(B,A),"Gap Day",[Date]) VAR C = SELECTCOLUMNS(A,"End Date",[Day Date]) --- Get a bunch of possible sequences VAR D = GENERATE( A, FILTER( C, [End Date] >= [Day Date]) ) -- Make a list of sequences WTIH a gap in them VAR E = SELECTCOLUMNS( GENERATE( D , FILTER( Gaps, [Gap Day]>=[Day Date] && [Gap Day] <= [End Date]) ), "Day Date",[Day Date] , "End Date",[End Date]) VAR F = EXCEPT(D,E) VAR G = SELECTCOLUMNS( GENERATE( F, FILTER( SELECTCOLUMNS( f, "D",[Day Date], "E",[End Date] ) , ([D]>[Day Date] && [D]<=[End Date]) || ([E]>=[Day Date] && [E]<[End Date]) ) ) ,"D",[D],"E",[E]) VAR H = SELECTCOLUMNS( EXCEPT(F,G), "Output", IF( [Day Date] = [End Date], FORMAT([Day Date],"DD/MM/YYYY"), FORMAT([Day Date],"DD/MM") & "-" & FORMAT([End Date],"DD/MM/YYYY") )) RETURN CONCATENATEX(H,[Output], ", ")Compresses this data
Into this
Phew!
Microsoft EmployeeHi IMarvinTPA
DAX can compress vertical sequences, but it's not super optimised for it.
This is pretty close, but you can see it's not a trivial peice of code.
But this measure.
Date Measure =
VAR A = VALUES('DD'[Day Date])
VAR B = CALENDAR(MIN('DD'[Day Date]),MAX('DD'[Day Date]))
VAR Gaps = SELECTCOLUMNS(EXCEPT(B,A),"Gap Day",[Date])
VAR C = SELECTCOLUMNS(A,"End Date",[Day Date])
--- Get a bunch of possible sequences
VAR D = GENERATE(
A,
FILTER(
C,
[End Date] >= [Day Date])
)
-- Make a list of sequences WTIH a gap in them
VAR E = SELECTCOLUMNS(
GENERATE(
D ,
FILTER(
Gaps,
[Gap Day]>=[Day Date] && [Gap Day] <= [End Date])
),
"Day Date",[Day Date] ,
"End Date",[End Date])
VAR F = EXCEPT(D,E)
VAR G =
SELECTCOLUMNS(
GENERATE(
F,
FILTER(
SELECTCOLUMNS(
f,
"D",[Day Date],
"E",[End Date]
) ,
([D]>[Day Date] && [D]<=[End Date])
||
([E]>=[Day Date] && [E]<[End Date])
)
)
,"D",[D],"E",[E])
VAR H =
SELECTCOLUMNS(
EXCEPT(F,G),
"Output",
IF(
[Day Date] = [End Date],
FORMAT([Day Date],"DD/MM/YYYY"),
FORMAT([Day Date],"DD/MM") & "-" & FORMAT([End Date],"DD/MM/YYYY")
))
RETURN CONCATENATEX(H,[Output], ", ")Compresses this data
Into this
Phew!
Wow.
I knew it wasn't going to be easy. I'll be studying this for a while. The mindset it takes to operate in this direction is very different from a procedural one.
Thank you!
IMarv
Tried it with more data and it results in a farily interesting expansion of values. I'll see if I can tweak it to actually work.
April 1-28th looks like (No data on the 8th, and 15):
04/01-04/02/2018, 04/01-04/03/2018, 04/02-04/03/2018, 04/01-04/04/2018, 04/02-04/04/2018, 04/03-04/04/2018, 04/01-04/05/2018, 04/02-04/05/2018, 04/03-04/05/2018, 04/04-04/05/2018, 04/01-04/07/2018, 04/02-04/07/2018, 04/03-04/07/2018, 04/04-04/07/2018, 04/05-04/07/2018, 04/06-04/07/2018, 04/01-04/06/2018, 04/02-04/06/2018, 04/03-04/06/2018, 04/04-04/06/2018, 04/05-04/06/2018, 04/01-04/09/2018, 04/02-04/09/2018, 04/03-04/09/2018, 04/04-04/09/2018, 04/05-04/09/2018, 04/07-04/09/2018, 04/06-04/09/2018, 04/01-04/10/2018, 04/02-04/10/2018, 04/03-04/10/2018, 04/04-04/10/2018, 04/05-04/10/2018, 04/07-04/10/2018, 04/06-04/10/2018, 04/09-04/10/2018, 04/01-04/11/2018, 04/02-04/11/2018, 04/03-04/11/2018, 04/04-04/11/2018, 04/05-04/11/2018, 04/07-04/11/2018, 04/06-04/11/2018, 04/09-04/11/2018, 04/10-04/11/2018, 04/01-04/12/2018, 04/02-04/12/2018, 04/03-04/12/2018, 04/04-04/12/2018, 04/05-04/12/2018, 04/07-04/12/2018, 04/06-04/12/2018, 04/09-04/12/2018, 04/10-04/12/2018, 04/11-04/12/2018, 04/01-04/13/2018, 04/02-04/13/2018, 04/03-04/13/2018, 04/04-04/13/2018, 04/05-04/13/2018, 04/07-04/13/2018, 04/06-04/13/2018, 04/09-04/13/2018, 04/10-04/13/2018, 04/11-04/13/2018, 04/12-04/13/2018, 04/01-04/14/2018, 04/02-04/14/2018, 04/03-04/14/2018, 04/04-04/14/2018, 04/05-04/14/2018, 04/07-04/14/2018, 04/06-04/14/2018, 04/09-04/14/2018, 04/10-04/14/2018, 04/11-04/14/2018, 04/12-04/14/2018, 04/13-04/14/2018, 04/01-04/16/2018, 04/02-04/16/2018, 04/03-04/16/2018, 04/04-04/16/2018, 04/05-04/16/2018, 04/07-04/16/2018, 04/06-04/16/2018, 04/09-04/16/2018, 04/10-04/16/2018, 04/11-04/16/2018, 04/12-04/16/2018, 04/13-04/16/2018, 04/14-04/16/2018, 04/01-04/17/2018, 04/02-04/17/2018, 04/03-04/17/2018, 04/04-04/17/2018, 04/05-04/17/2018, 04/07-04/17/2018, 04/06-04/17/2018, 04/09-04/17/2018, 04/10-04/17/2018, 04/11-04/17/2018, 04/12-04/17/2018, 04/13-04/17/2018, 04/14-04/17/2018, 04/16-04/17/2018, 04/01-04/18/2018, 04/02-04/18/2018, 04/03-04/18/2018, 04/04-04/18/2018, 04/05-04/18/2018, 04/07-04/18/2018, 04/06-04/18/2018, 04/09-04/18/2018, 04/10-04/18/2018, 04/11-04/18/2018, 04/12-04/18/2018, 04/13-04/18/2018, 04/14-04/18/2018, 04/16-04/18/2018, 04/17-04/18/2018, 04/01-04/19/2018, 04/02-04/19/2018, 04/03-04/19/2018, 04/04-04/19/2018, 04/05-04/19/2018, 04/07-04/19/2018, 04/06-04/19/2018, 04/09-04/19/2018, 04/10-04/19/2018, 04/11-04/19/2018, 04/12-04/19/2018, 04/13-04/19/2018, 04/14-04/19/2018, 04/16-04/19/2018, 04/17-04/19/2018, 04/18-04/19/2018, 04/01-04/20/2018, 04/02-04/20/2018, 04/03-04/20/2018, 04/04-04/20/2018, 04/05-04/20/2018, 04/07-04/20/2018, 04/06-04/20/2018, 04/09-04/20/2018, 04/10-04/20/2018, 04/11-04/20/2018, 04/12-04/20/2018, 04/13-04/20/2018, 04/14-04/20/2018, 04/16-04/20/2018, 04/17-04/20/2018, 04/18-04/20/2018, 04/19-04/20/2018, 04/01-04/21/2018, 04/02-04/21/2018, 04/03-04/21/2018, 04/04-04/21/2018, 04/05-04/21/2018, 04/07-04/21/2018, 04/06-04/21/2018, 04/09-04/21/2018, 04/10-04/21/2018, 04/11-04/21/2018, 04/12-04/21/2018, 04/13-04/21/2018, 04/14-04/21/2018, 04/16-04/21/2018, 04/17-04/21/2018, 04/18-04/21/2018, 04/19-04/21/2018, 04/20-04/21/2018, 04/01-04/23/2018, 04/02-04/23/2018, 04/03-04/23/2018, 04/04-04/23/2018, 04/05-04/23/2018, 04/07-04/23/2018, 04/06-04/23/2018, 04/09-04/23/2018, 04/10-04/23/2018, 04/11-04/23/2018, 04/12-04/23/2018, 04/13-04/23/2018, 04/14-04/23/2018, 04/16-04/23/2018, 04/17-04/23/2018, 04/18-04/23/2018, 04/19-04/23/2018, 04/20-04/23/2018, 04/21-04/23/2018, 04/22-04/23/2018, 04/01-04/22/2018, 04/02-04/22/2018, 04/03-04/22/2018, 04/04-04/22/2018, 04/05-04/22/2018, 04/07-04/22/2018, 04/06-04/22/2018, 04/09-04/22/2018, 04/10-04/22/2018, 04/11-04/22/2018, 04/12-04/22/2018, 04/13-04/22/2018, 04/14-04/22/2018, 04/16-04/22/2018, 04/17-04/22/2018, 04/18-04/22/2018, 04/19-04/22/2018, 04/20-04/22/2018, 04/21-04/22/2018, 04/01-04/24/2018, 04/02-04/24/2018, 04/03-04/24/2018, 04/04-04/24/2018, 04/05-04/24/2018, 04/07-04/24/2018, 04/06-04/24/2018, 04/09-04/24/2018, 04/10-04/24/2018, 04/11-04/24/2018, 04/12-04/24/2018, 04/13-04/24/2018, 04/14-04/24/2018, 04/16-04/24/2018, 04/17-04/24/2018, 04/18-04/24/2018, 04/19-04/24/2018, 04/20-04/24/2018, 04/21-04/24/2018, 04/23-04/24/2018, 04/22-04/24/2018, 04/01-04/25/2018, 04/02-04/25/2018, 04/03-04/25/2018, 04/04-04/25/2018, 04/05-04/25/2018, 04/07-04/25/2018, 04/06-04/25/2018, 04/09-04/25/2018, 04/10-04/25/2018, 04/11-04/25/2018, 04/12-04/25/2018, 04/13-04/25/2018, 04/14-04/25/2018, 04/16-04/25/2018, 04/17-04/25/2018, 04/18-04/25/2018, 04/19-04/25/2018, 04/20-04/25/2018, 04/21-04/25/2018, 04/23-04/25/2018, 04/22-04/25/2018, 04/24-04/25/2018, 04/01-04/26/2018, 04/02-04/26/2018, 04/03-04/26/2018, 04/04-04/26/2018, 04/05-04/26/2018, 04/07-04/26/2018, 04/06-04/26/2018, 04/09-04/26/2018, 04/10-04/26/2018, 04/11-04/26/2018, 04/12-04/26/2018, 04/13-04/26/2018, 04/14-04/26/2018, 04/16-04/26/2018, 04/17-04/26/2018, 04/18-04/26/2018, 04/19-04/26/2018, 04/20-04/26/2018, 04/21-04/26/2018, 04/23-04/26/2018, 04/22-04/26/2018, 04/24-04/26/2018, 04/25-04/26/2018, 04/01-04/27/2018, 04/02-04/27/2018, 04/03-04/27/2018, 04/04-04/27/2018, 04/05-04/27/2018, 04/07-04/27/2018, 04/06-04/27/2018, 04/09-04/27/2018, 04/10-04/27/2018, 04/11-04/27/2018, 04/12-04/27/2018, 04/13-04/27/2018, 04/14-04/27/2018, 04/16-04/27/2018, 04/17-04/27/2018, 04/18-04/27/2018, 04/19-04/27/2018, 04/20-04/27/2018, 04/21-04/27/2018, 04/23-04/27/2018, 04/22-04/27/2018, 04/24-04/27/2018, 04/25-04/27/2018, 04/26-04/27/2018, 04/01-04/28/2018, 04/02-04/28/2018, 04/03-04/28/2018, 04/04-04/28/2018, 04/05-04/28/2018, 04/07-04/28/2018, 04/06-04/28/2018, 04/09-04/28/2018, 04/10-04/28/2018, 04/11-04/28/2018, 04/12-04/28/2018, 04/13-04/28/2018, 04/14-04/28/2018, 04/16-04/28/2018, 04/17-04/28/2018, 04/18-04/28/2018, 04/19-04/28/2018, 04/20-04/28/2018, 04/21-04/28/2018, 04/23-04/28/2018, 04/22-04/28/2018, 04/24-04/28/2018, 04/25-04/28/2018, 04/26-04/28/2018, 04/27-04/28/2018
Thanks,
IMarv
For some reason the
SELECTCOLUMNS( f, "D",[Day Date], "E",[End Date] )in
VAR G = SELECTCOLUMNS( GENERATE( F, FILTER( SELECTCOLUMNS( f, "D",[Day Date], "E",[End Date] ) , ([D]>[Day Date] && [D]<=[End Date]) || ([E]>=[Day Date] && [E]<[End Date]) ) ) ,"D",[D],"E",[E])was behaving as if it were
SELECTCOLUMNS( f, "D",[Day Date], "E",[Day Date] )Resulting in a bad comparison.
I eventually fixed it by replacing it with
SELECTCOLUMNS(F, "E", Date(Year([End Date]), MONTH([End Date]), Day([End Date])), "D", [Day Date])
Edit: Also had to update E:
-- Make a list of sequences WTIH a gap in them VAR E = SELECTCOLUMNS( GENERATE( D , FILTER( Gaps, [Gap Day]>=[Day Date] && [Gap Day] <= [End Date]) ), "Day Date", [Day Date] , "End Date", Date(Year([End Date]), MONTH([End Date]), Day([End Date])))Which forced it to do it correctly.
I'm not sure what is wrong with it internally, but this seems to be a case of me fighting an environment bug.
Thanks,
IMarv
- Phil_Seamark
I think I know what the problem is and I can test a change locally.
I think the SELECTCOLUMNS function is getting the order randomly around the wrong way. I think if I nest using ADDCOLUMNS it should make it more robust (the column ordering)