Forum Discussion

PBI5851's avatar
PBI5851
Helper V
4 years ago
Solved

Rolling counts-ish

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)

 

PriMKeyItemIDOrderDateMaxOrderdate
1abc1231/15/20211/31/2021
2abc1231/15/20211/31/2021
3abc1231/15/20211/31/2021
4def1231/22/20212/16/2022
5def1231/22/20212/16/2022
6def1231/22/20212/16/2022
7def1231/22/20212/16/2022
8efg1234/10/20217/31/2021
9efg1234/10/20217/31/2021
10efg1234/10/20217/31/2021
11fgh12311/30/202111/30/2021
12ghi12312/22/20212/16/2022
13hij1231/30/20222/16/2022
14hij1231/30/20222/16/2022
15hij1231/30/20222/16/2022

 

I hope to achieve two cross tabs (monthly & quarterly)

 janfebmarch aprmayjunjulaugsepoctnovdec
2021211222211122
202233          

 

 Q1Q2Q3Q4
20212223
20222   

 

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 

UniqueitemCount = CALCULATE( DISTINCTCOUNT('Table'[itemID]), FILTER('Table', 'Table'[Maxorderdatedate].[Date] < ALLSELECTED(Datetable[Date].[Date])))

 

Any recommendations on how to resolve this please. 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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]
    )
    )

     

2 Replies