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.
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
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 |
---|---|
96 | |
94 | |
82 | |
70 | |
64 |
User | Count |
---|---|
116 | |
106 | |
94 | |
79 | |
72 |