Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hello,
with the below data, i am trying to count how many unique items per month/quarter were open (i.e they must fall between orderdate and maxorderdate)
PriMKey | ItemID | OrderDate | MaxOrderdate |
1 | abc123 | 1/15/2021 | 1/31/2021 |
2 | abc123 | 1/15/2021 | 1/31/2021 |
3 | abc123 | 1/15/2021 | 1/31/2021 |
4 | def123 | 1/22/2021 | 2/16/2022 |
5 | def123 | 1/22/2021 | 2/16/2022 |
6 | def123 | 1/22/2021 | 2/16/2022 |
7 | def123 | 1/22/2021 | 2/16/2022 |
8 | efg123 | 4/10/2021 | 7/31/2021 |
9 | efg123 | 4/10/2021 | 7/31/2021 |
10 | efg123 | 4/10/2021 | 7/31/2021 |
11 | fgh123 | 11/30/2021 | 11/30/2021 |
12 | ghi123 | 12/22/2021 | 2/16/2022 |
13 | hij123 | 1/30/2022 | 2/16/2022 |
14 | hij123 | 1/30/2022 | 2/16/2022 |
15 | hij123 | 1/30/2022 | 2/16/2022 |
I hope to achieve two cross tabs (monthly & quarterly)
jan | feb | march | apr | may | jun | jul | aug | sep | oct | nov | dec | |
2021 | 2 | 1 | 1 | 2 | 2 | 2 | 2 | 1 | 1 | 1 | 2 | 2 |
2022 | 3 | 3 |
Q1 | Q2 | Q3 | Q4 | |
2021 | 2 | 2 | 2 | 3 |
2022 | 2 |
Ex: For 2021 Apr - i am counting def123 and efg123
For 2021 Nov - I am counting def123, fgh123.
For Q4 of 2021- I am counting def123, fgh123 and ghi123.
P.S on the MaxOrderdate, its basically today(). I am trying to use
Any recommendations on how to resolve this please.
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Unique count item: =
CALCULATE (
DISTINCTCOUNT ( Data[ItemID] ),
FILTER (
Data,
MAX ( 'Calendar'[Date] ) >= Data[OrderDate]
&& MIN ( 'Calendar'[Date] ) <= Data[MaxOrderdate]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
Please check the below picture and the attached pbix file.
Unique count item: =
CALCULATE (
DISTINCTCOUNT ( Data[ItemID] ),
FILTER (
Data,
MAX ( 'Calendar'[Date] ) >= Data[OrderDate]
&& MIN ( 'Calendar'[Date] ) <= Data[MaxOrderdate]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@PBI5851 , Refe blog on similar topic
File use example with active join to date table
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |