Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 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...
  • OwenAuger's avatar
    OwenAuger
    7 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

OwenAuger's avatar
OwenAuger
Super User
7 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
      Super 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