Converting stages with dates into a timeline chart.

[Anonymous]

I have some data which looks like the following: 

 

What I want to do is create a chart which will show me how many ideas were in each stage on that particular date.

e.g. on 31/7/2018, we had 2 ideas in stage 2(A,B) and nothing else.

While on 16/9/2018, we had 1 in Stage 3(A), 1 in Stage 2(B), and 2 in Stage 1(C,D)

Is there a way to achieve this dynamically? (i.e. not specifying a date and then using 'countif' to try and figure it out as this is intended to be used as a dynamic dashbard)

The final output will be a stacked column chart with dates along the x axis and counts of the stages on the y axis.

[OwenAuger · Accepted solution]

Hi @Anonymous

Here is one approach - uploaded pbix here.

1. Transform your Ideas table into this form:

2. Create a disconnected 'Date' table

3. Create this measure:

   Idea Count as at Latest Stage =
   VAR MaxDate =
       MAX ( 'Date'[Date] )
   RETURN
       CALCULATE (
           DISTINCTCOUNT ( Ideas[Idea] ),
           GENERATE (
               VALUES ( Ideas[Idea] ),
               CALCULATETABLE (
                   LASTDATE ( Ideas[Date] ),
                   ALLEXCEPT ( Ideas, Ideas[Idea] ),
                   Ideas[Date] <= MaxDate
               )
           )
       )

    This measure creates a filter combining each Idea with its latest Date. When you apply a Stage filter and a 'Date'[Date] filter, you can then count the Ideas for which the filtered Stage(s) are the latest stage as at the latest Date filtered.

4. Stacked column visual looks like this - I think you wanted something similar:

Is that the sort of thing you were looking for?

Regards,

Owen

[Anonymous]

Hey Owen, 

This is almost right, unfortunately, if the project goes through two stages on the same date, it doesn't seem to pick the later stage. Instead it counts 1 for both stages. Is there a way to make it only pick the later stage? 

[OwenAuger]

@Anonymous

I see the problem.

I have updated my file with a couple of options for measures that correct for this:
PBIX link

Logic is the same in each, just written slightly differently. There may well be a more elegant way of handling this! 🙂

Idea Count as at Latest Stage V2 = 
VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Ideas[Idea] ),
        KEEPFILTERS (
            GENERATE (
                GENERATE (
                    VALUES ( Ideas[Idea] ),
                    CALCULATETABLE (
                        LASTDATE ( Ideas[Date] ),
                        ALLEXCEPT ( Ideas, Ideas[Idea] ),
                        Ideas[Date] <= MaxDate
                    )
                ),
                CALCULATETABLE (
                    LASTNONBLANK ( Ideas[Stage], 0 ),
                    ALLEXCEPT ( Ideas, Ideas[Idea], Ideas[Date] )
                )
            )
        )
    )

Idea Count as at Latest Stage V3 = 
VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Ideas[Idea] ),
        KEEPFILTERS (
            GENERATE (
                VALUES ( Ideas[Idea] ),
                CALCULATETABLE (
                    GENERATE (
                        LASTDATE ( Ideas[Date] ),
                        CALCULATETABLE (
                            LASTNONBLANK ( Ideas[Stage], 0 ),
                            ALLEXCEPT ( Ideas, Ideas[Idea], Ideas[Date] )
                        )
                    ),
                    ALLEXCEPT ( Ideas, Ideas[Idea] ),
                    Ideas[Date] <= MaxDate
                )
            )
        )
    )

Regards,

Owen