topic Re: Measure help needed - Count values for each period in DAX Commands and Tips

Measure help needed - Count values for each period

Anonymous — Tue, 06 Oct 2020 11:26:19 GMT

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

Re: Measure help needed - Count values for each period

Anonymous — Tue, 06 Oct 2020 13:41:58 GMT

// 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

Re: Measure help needed - Count values for each period

Anonymous — Tue, 06 Oct 2020 13:44:49 GMT

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

Thank you!

Re: Measure help needed - Count values for each period

Anonymous — Wed, 07 Oct 2020 11:07:53 GMT

Worked perfectly! Thanks!