Graph axis = two columns filtered

[Anonymous]

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?

[VasTg · Accepted solution]

@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]

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]

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

[Anonymous]

Oh no that's not what I meant

I want to print all the ids that were successfully filtered in that other column

Ex: if the count is 4, I wanna print 123;456;789;147

Doesn't really matter the way it's printed, if it's all in the same column as in "123;456" or one id in every column, but I need the values

[Anonymous]

Thank you! That worked perfectly