Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
We have clients with three relevent dates (application, start, and stop). I've made a seperate date table so now the report has three matrices (one for each count). How can I get a distinct count of clients if their date fell in the larger date range? i.e. I want a disinct count of clients who applied in the date range, a seperate distinct ount of clients who started in the range, and then a distinct count of clients who ended in the range.
In a formula it would be IF([app date] >= [period start] & [app date] <= [period end], 1, 0) and so on but that requires me to make two seperate date columns that I change for the viewers. I need three counts, using different dates, filtered by one slicer my viewers change. If they occured in the timeframe it is a 1 otherwise a 0.
I created some eample data below
clients | App date | Start Date | stop date |
1 | 1/1/2021 | 2/1/2021 | 6/1/2021 |
2 | 1/1/2022 | 1/16/2022 | 1/16/2023 |
3 | 1/1/2023 | 2/1/2023 | 1/31/2025 |
4 | 1/1/2022 | 3/4/2022 | 6/2/2022 |
5 | 1/1/2022 | 4/1/2022 | 5/31/2022 |
6 | 1/1/2023 | 1/21/2023 | 4/26/2023 |
7 | 1/1/2023 | 5/1/2023 | |
8 | 1/1/2024 | 6/29/2024 | |
9 | 1/1/2023 | 1/16/2023 | 3/17/2023 |
10 | 1/1/2022 | 1/1/2023 | 3/17/2023 |
11 | 1/1/2021 | 6/30/2021 | |
12 | 1/1/2022 | 4/1/2022 | 7/10/2022 |
13 | 1/1/2024 | 2/1/2021 | 3/18/2021 |
14 | 1/1/2023 | 1/16/2023 | 4/16/2023 |
15 | 1/1/2024 | 2/1/2024 | 5/31/2024 |
16 | 1/1/2022 | 3/4/2022 | 3/4/2023 |
17 | 1/1/2022 | 4/1/2022 | 3/31/2024 |
18 | 1/1/2020 | 1/21/2020 | 3/21/2020 |
19 | 1/1/2022 | 5/1/2022 | 8/4/2022 |
20 | 1/1/2023 | ||
21 | 1/1/2023 | 1/16/2023 | 3/26/2025 |
22 | 1/1/2023 | 1/1/2024 | 1/31/2024 |
23 | 1/1/2023 | ||
24 | 1/1/2023 | 4/1/2023 | 6/30/2023 |
Solved! Go to Solution.
Hi @BW40 ,
Please try to create a date table for slicer visual:
Table 2 = CALENDAR(dt"2023-01-01",dt"2023-12-01")
Create a measure with below dax formula:
Measure =
VAR _min =
MIN ( 'Table 2'[Date] )
VAR _max =
MAX ( 'Table 2'[Date] )
VAR tmp =
CALENDAR ( _min, _max )
VAR tmp1 =
FILTER ( ALL ( 'Table' ), [App date] IN tmp )
VAR tmp2 =
FILTER ( ALL ( 'Table' ), [Start Date] IN tmp )
VAR tmp3 =
FILTER ( ALL ( 'Table' ), [stop date] IN tmp )
VAR _a =
CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp1 )
VAR _b =
CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp2 )
VAR _c =
CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp3 )
RETURN
"Count 1:" & _a & "
" & "Count 2:" & _b & "
" & "Count 3:" & _c
Add a slicer visual and card visual:
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @BW40 ,
Please try to create a date table for slicer visual:
Table 2 = CALENDAR(dt"2023-01-01",dt"2023-12-01")
Create a measure with below dax formula:
Measure =
VAR _min =
MIN ( 'Table 2'[Date] )
VAR _max =
MAX ( 'Table 2'[Date] )
VAR tmp =
CALENDAR ( _min, _max )
VAR tmp1 =
FILTER ( ALL ( 'Table' ), [App date] IN tmp )
VAR tmp2 =
FILTER ( ALL ( 'Table' ), [Start Date] IN tmp )
VAR tmp3 =
FILTER ( ALL ( 'Table' ), [stop date] IN tmp )
VAR _a =
CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp1 )
VAR _b =
CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp2 )
VAR _c =
CALCULATE ( DISTINCTCOUNT ( 'Table'[clients] ), tmp3 )
RETURN
"Count 1:" & _a & "
" & "Count 2:" & _b & "
" & "Count 3:" & _c
Add a slicer visual and card visual:
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Took a little adjusting but that appears to have worked. I needed to break it down by further who worked with the clients and the employees' supervisors so I dropped the "ALL" part of the DAX so get the specific counts rather than only the totals.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
92 | |
86 | |
76 | |
64 |
User | Count |
---|---|
137 | |
113 | |
107 | |
98 | |
92 |