Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Graph axis = two columns filtered

Hello,

 

I have a database with three columns: id, date created and end date. I need to make a graph by month/year and the value is the count of ids that its date created is before or equal to that month/year of the axis and the end date is after or equal to it.

 

Example: 

id        |    date created    |    end date

123           05/08/2019        12/10/2019
456           02/07/2019        18/12/2019

In aug/19 the count is 2.

In jul/19 the count is 1 (only 456)
In dec/19 the count is 1 (only 456)

I can't find a way to do this because the axis is not a column of the database, and the filters are set to a fixed date, it's not dynamic (so the end date is always set to after the date created).

Can someone help me with this?

Need Help
Tutorial Requests
  • VasTg's avatar
    VasTg
    6 years ago

    Anonymous 

     

    Hope you have a date dimension.

     

    The method i know is to create a new column in Date dimension as below.

     

    Column =
CALCULATE (
    COUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[date created] <= 'Table 2'[Date]
            && 'Table'[end date] >= 'Table 2'[Date]
    )
)

     

     

    Now use the month column in axis and (Max of New_column) as value in the graph.

    If this helps, mark it as a solution

    Kudos are nice too.

5 Replies

  • VasTg's avatar
    VasTg
    Memorable Member
    6 years ago

    Anonymous 

     

    Hope you have a date dimension.

     

    The method i know is to create a new column in Date dimension as below.

     

    Column =
CALCULATE (
    COUNT ( 'Table'[id] ),
    FILTER (
        'Table',
        'Table'[date created] <= 'Table 2'[Date]
            && 'Table'[end date] >= 'Table 2'[Date]
    )
)

     

     

    Now use the month column in axis and (Max of New_column) as value in the graph.

    If this helps, mark it as a solution

    Kudos are nice too.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you! That worked perfectly

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      That solved it, yes, thank you.

       

      But if I want to have the ids that were filtered, how would I do it? I tried adding a new column but I can't get around the "the expression refers to multiple columns" error.

      • VasTg's avatar
        VasTg
        Memorable Member
        6 years ago

          

        Add it to the filter condtition.

         

         

        CALCULATE(COUNT('Table'[id]),FILTER('Table','Table'[date created]<='Table 2'[Date] && 'Table'[end date]>='Table 2'[Date] && 'Table'[id] = 123))

         

         

        If it helps, mark it as a solution

        Kudos are nice too

         

         

        Anonymous