Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Converting stages with dates into a timeline chart.

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's avatar
    OwenAuger
    8 years ago

    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

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        OwenAuger
        Icon for Super User rankSuper User
        7 years ago

        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