Measure help needed - Count values for each period

[Anonymous]

Hello, 

Can anyone help me please to create measure with following scenario?

I have table:

Name	Date start	Date end
ABC	05.07.2020	28.09.2020
CDE	15.08.2020	28.08.2020

What i need is to count "Name" in each period between date start and date end. And then crate chart with data distributed like 

in table below:

Year	Month	Qty
2020	July	1
2020	August	2
2020	September	1

Total for Year 2020 should be equal to 2. 

Will appreciate any help. 

Thanks

[Anonymous · Accepted solution]

// T is your table with
// Name|DateStart|DateEnd
// You also should have a Dates
// table in the model that
// is the calendar date. Dates
// should NOT be connected to
// T in any way. Then you can write:

[Qty] =
var __minDate = min( Dates[Date] )
var __maxDate = max( Dates[Date] )
var __output =
    // This works on condition that
    // the T table stores different
    // Names on each row. If this is
    // not the case, then you have to
    // use the (2) formula.
    COUNTROWS(
        filter(
            T,
            var __dateStart = T[DateStart]
            var __dateEnd = T[DateEnd]
            return
            NOT ISEMPTY(
                filter(
                    Dates,
                    __dateStart <= Dates[Date]
                    &&
                    Dates[Date] <= __dateEnd
                )
            )
        )
    )
/* (2)
var __output =
    CALCULATE(
        DISTINCTCOUNT( T[Name] ),
        filter(
            T,
            var __dateStart = T[DateStart]
            var __dateEnd = T[DateEnd]
            return
            NOT ISEMPTY(
                filter(
                    Dates,
                    __dateStart <= Dates[Date]
                    &&
                    Dates[Date] <= __dateEnd
                )
            )
        )        
    )
*/
return
    __output

[Anonymous]

Worked perfectly! Thanks!

[Anonymous]

Wow, thanks, i will test is now and post results soon!

Thank you!