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.
Dear community,
I have 2 tables, a "Fiscal Year" date table and a table with items that change their status over time (see below) e.g.
Status open when there is a " date created" and Status closed when there is a "Closed date".
I need to be able to count the number of items that were "open" based on the date I filter:
Example below:
If I filter on October 8th 2022 it should count:
All items that have a "Date Created" => 10.08.22 with no "Closed Date" (the ones with the status Open)
+
all with a "closed Date" > 10.08.22 && "Date Created" <= 10.08.22 (The ones with the status filled but had the status "open" on the date of filter)
Somehow I have not been lucky with several solutions from slightly similar issues in the community.
Thank you very much in advance for any clue!!
Svendu
Solved! Go to Solution.
Hi, @Svendu
You can try the following methods.
Measure =
VAR _N1 =CALCULATE (COUNT ( 'Table'[ID Number] ),
FILTER (ALL ( 'Table' ),
[Date Created] >= SELECTEDVALUE ( 'Date'[Date] )
&& [Closed Date] = BLANK ()
&& [Status] = "Open"
)
)
VAR _N2 =CALCULATE (COUNT ( 'Table'[ID Number] ),
FILTER (ALL ( 'Table' ),
[Date Created] <= SELECTEDVALUE ( 'Date'[Date] )
&& [Closed Date] >= SELECTEDVALUE ( 'Date'[Date] )
&& [Status] = "Filled"
)
)
RETURN
_N1 + _N2
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you very much for your prompt and helpful reply on this issue.
Svendu
Hi, @Svendu
You can try the following methods.
Measure =
VAR _N1 =CALCULATE (COUNT ( 'Table'[ID Number] ),
FILTER (ALL ( 'Table' ),
[Date Created] >= SELECTEDVALUE ( 'Date'[Date] )
&& [Closed Date] = BLANK ()
&& [Status] = "Open"
)
)
VAR _N2 =CALCULATE (COUNT ( 'Table'[ID Number] ),
FILTER (ALL ( 'Table' ),
[Date Created] <= SELECTEDVALUE ( 'Date'[Date] )
&& [Closed Date] >= SELECTEDVALUE ( 'Date'[Date] )
&& [Status] = "Filled"
)
)
RETURN
_N1 + _N2
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |