The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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
// 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
Worked perfectly! Thanks!
Wow, thanks, i will test is now and post results soon!
Thank you!
User | Count |
---|---|
28 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |