Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Looking for some help with a measure I am working on and can't seem to figure out.
I have a contract table with contractid, startdate, enddate columns. I need to count contracts that were/are active at any point during a date range specificed on the reports date range slicer.
I have tried approached including datesbetween, IF statements, and Filters, but am stuck. Any help is much appreciated.
contractid | Startdate | Enddate |
123456 | 11/01/2017 | 11/31/2018 |
112233 | 12/15/2017 | 03/03/2018 |
144523 | 01/01/2018 | 01/01/2019 |
So If the date range slicer was set for 11/05/2017 - 01/05/2018 the count would show 2
Solved! Go to Solution.
Hi @antbob ,
Here we can create a measure as below to get the excepted result we need.
Measure = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, Table1[Startdate] > MIN ( 'Table'[Date] ) ) )
Please find the pbix as attached.
Regards,
Frank
In addition to having the number of contracts for the date range, how to show the list of contract available?
Hi,
My suggestion would be to convert the 3 column dataset into a 2 column dataset - ContractID and Date. Then build a Calendar Table with a relationship. That way when one selects a data range (built from the Calendar Table) in a slicer, only those ContractID's would show up.
Hi @antbob ,
Here we can create a measure as below to get the excepted result we need.
Measure = CALCULATE ( COUNTROWS ( Table1 ), FILTER ( Table1, Table1[Startdate] > MIN ( 'Table'[Date] ) ) )
Please find the pbix as attached.
Regards,
Frank
Thanks @v-frfei-msft,
This is certainly closer then I was able to get, and spawned a light bulb moment of understanding. The end goal is to be able to say how many contracts were active say in the last 3 months, or even the last 6 months given variable length contracts
Here is what I ended up with, which I have yet to fully validate, but a few quick spot checks seemed accurate
Measure = CALCULATE (
COUNTROWS(Table1 ),
FILTER( Table1, ( Table1[StartDate] >= MIN ( Table[Date] ) && Table1[StartDate] < MAX ( Table[Date] ) )||( Table1[EndDate] > MIN ( Table[Date]) && Table1[EndDate] <= MAX ( Table[Date] ) )||( Table1[StartDate] < MIN ( Table[Date]) && Table1[EndDate] > MAX ( Table[Date] ) ) ) )
here is my suggestion to tackle this :
first Make a new date table.
DateTable = Calendar(Minx(table1,Table1[Date]),Now())
Now add a new calculated column:
DateSlicer = CALCULATE( COUNTROWS(contractid), FILTER(Table1, DateTable[Date] >= [startdate] && DateTable[Date] <= [Enddate])
Hope this will help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |