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.
I have a set of data that has one record per OrderItemId which is a specific unit of a sale. I have two columns, CONDATE which is the begging date of this sale and FINALDATE which is when the sale closed in some way. I also have a separate date table.
What I want is to do is have a date field that a user enters and a measure returns how many OrderItemIDs were "active" for that specific date.
For example - if the date selected by the user was 1/3/2020, the result would be 2 since orders 1234001 & 1234002 had been ordered but not yet finalized. If the date was 1/5/2020 then the result would be 4. If the date was 1/8/2020 the result would be 3 since OrderItemID 1234003 had hit a FINALDATE of 1/6, so this order would be excluded.
OrderItemID | CONDATE | FINALDATE |
1234001 | 1/1/2020 | 1/30/2020 |
1234002 | 1/1/2020 | 1/30/2020 |
1234003 | 1/4/2020 | 1/6/2020 |
1234004 | 1/4/2020 | 1/30/2020 |
1234005 | 1/9/2020 | 1/30/2020 |
1234006 | 1/9/2020 | 1/30/2020 |
Solved! Go to Solution.
Hi @awitt ,
We can try to use the following measure to meet your requirement:
Count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[OrderItemID] ),
FILTER (
'Table',
OR (
ISBLANK ( 'Table'[FINALDATE] ),
NOT (
OR (
'Table'[CONDATE] > MAX ( 'DateSlicer'[Date] ),
'Table'[FINALDATE] <= MIN ( 'DateSlicer'[Date] )
)
)
)
)
)
Best regards,
Hi @awitt ,
We can create a calculated table and a measure to meet your requirement:
Calculated table:
DateSlicer = CALENDAR(DATE(2019,1,1),DATE(2021,12,31))
Measure:
Count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[OrderItemID] ),
FILTER (
'Table',
NOT (
OR (
'Table'[CONDATE] > MAX ( 'DateSlicer'[Date] ),
'Table'[FINALDATE] <= MIN ( 'DateSlicer'[Date] )
)
)
)
)
By the way, PBIX file as attached.
Best regards,
@v-lid-msft I'm pretty sure this is going to work for my dataset. The hard thing is proving the results. Is there a way to have a table that only returns the results of a given day as well?
For instance, I have some days where the result for that day is over 20,000. I know i'm going to be asked which 20,000 records made up that number but I haven't been able to do that yet. Thoughts?
Hi @awitt ,
We can put the measure into visual filter of table chart, then set it "is not blank" or "is not zero" to meet your requirement:
By the way, PBIX file as attached.
Best regards,
@v-lid-msft The only thing that seems off is that Blank FINALDATE values are not included in the measure and they should be. If this is essentially open orders, there wont be Final Dates for active orders that haven't yet been completed.
Hi @awitt ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hi @awitt ,
We can try to use the following measure to meet your requirement:
Count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[OrderItemID] ),
FILTER (
'Table',
OR (
ISBLANK ( 'Table'[FINALDATE] ),
NOT (
OR (
'Table'[CONDATE] > MAX ( 'DateSlicer'[Date] ),
'Table'[FINALDATE] <= MIN ( 'DateSlicer'[Date] )
)
)
)
)
)
Best regards,
You will need something like Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |