Consolidating Dates in a Report Title.

[IMarvinTPA]

Hi,

I have a report that has daily data which can be filtered by date.  I also need to show the dates in the title.  I have that much sorted out using https://blog.crossjoin.co.uk/2016/04/25/dynamic-chart-titles-in-power-bi/

I have a measure of 

ListOfDays = Concatenatex(Values(DD[Day Date]), DD[Day Date], ", ", DD[Day Date], ASC)

But it is outputting data like

12/23/2017, 12/26/2017, 12/27/2017, 12/28/2017, 12/29/2017, 12/30/2017, ...

How would I do it such that the output would be:

12/23/2017, 12/26-30/2017, 1/3/2018, 1/5-7/2018, 1/9, 1/11

Basic rules, only show the year on the last half of a span.  Only show the year for a single date if it changes from the previous shown date. 

From what I can tell, Looping and building a string are not options in Power BI.

Thanks,

IMarv

[Vvelarde]

@IMarvinTPA

Hi, i recently read this interesting question.

My alternative to solve it will be:

Selected =
VAR SELECTIONS =
    VALUES ( Valores[Value] )
VAR SERIEWITHEEXTRACOLUMNS =
    ADDCOLUMNS ( SELECTIONS, "M-1", Valores[Value] - 1, "M+1", Valores[Value] + 1 )
VAR MINVALUESELECTED =
    MIN ( Valores[Value] )
RETURN
    IF (
        HASONEVALUE ( Valores[Value] ),
        FORMAT ( SELECTEDVALUE ( Valores[Value] ), "DD/MM/YYYY" ),
        IF (
            ISFILTERED ( Valores[Value] ),
            CONCATENATEX (
                SERIEWITHEEXTRACOLUMNS,
                IF (
                    COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M-1] ) )
                        > 0
                        && COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M+1] ) )
                            > 0,
                    BLANK (),
                    IF (
                        COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M+1] ) )
                            < 1
                            && COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M-1] ) )
                                > 0,
                        "-" & FORMAT ( Valores[Value], "DD/MM/YYYY" ),
                        IF (
                            Valores[Value] = MINVALUESELECTED,
                            FORMAT ( Valores[Value], "DD/MM" ),
                            IF (
                                COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M+1] ) )
                                    < 1
                                    && COUNTROWS ( FILTER ( SELECTIONS, Valores[Value] = [M-1] ) )
                                        < 1,
                                ", " & FORMAT ( Valores[Value], "DD/MM/YYYY" ),
                                ", " & FORMAT ( Valores[Value], "DD/MM" )
                            )
                        )
                    )
                )
            ),
            "NO SELECTED"
        )
    )

Regards

Victor

[Phil_Seamark · Accepted solution]

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!

[IMarvinTPA]

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

[IMarvinTPA]

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

[IMarvinTPA]

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)

[Phil_Seamark]

Hi @IMarvinTPA

EUREKA!!!

I found and fixed the problem.  The issue is to do with lineage and I suspect there are issues in the engine, but the fix is just to add a +0 to two  places in the code.  I have highlighed the two places in red, but basically at the end of the 2nd columns in both SELECTCOLUMN calls.

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] +0 )   
VAR F = EXCEPT(D,E)

VAR G = 
    SELECTCOLUMNS( 
        GENERATE(
            F,
            FILTER(
                SELECTCOLUMNS(
                    f,
                    "D",[Day Date] ,
                    "E",[End Date] +0
                    ) ,
                ([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], ", ")