Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello. I need some help.
Below is an example of my model:
I am trying to get a count of all transactions with a First Transaction Date before 01/02/2022 (based on slicer selection) using:
CALCULATE(DISTINCTCOUNT('Transaction Table'[Rreference]),
FILTER('Transaction Table','Transaction Table'[First Transaction Date]<MIN('Date Table'[Date].[Date])
However, the above gives me a count of 2, instead of 3, omitting reference 167 because it doesn’t have a transaction date in the month of February 2022.
Any help will be much appreciated.
Thanks
Solved! Go to Solution.
Hi @Kiki
Please try
=
CALCULATE (
DISTINCTCOUNT ( 'Transaction Table'[Rreference] ),
'Transaction Table'[First Transaction Date] < MIN ( 'Date Table'[Date] ),
CROSSFILTER ( 'Transaction Table'[Transaction Date], 'Date Table'[Date], NONE )
)
Hi @Kiki
Please try
=
CALCULATE (
DISTINCTCOUNT ( 'Transaction Table'[Rreference] ),
'Transaction Table'[First Transaction Date] < MIN ( 'Date Table'[Date] ),
CROSSFILTER ( 'Transaction Table'[Transaction Date], 'Date Table'[Date], NONE )
)
YES!! Thank you so much! It works!
☺️
@Kiki please try:
CALCULATE(DISTINCTCOUNT('Transaction Table'[Rreference]),
'Transaction Table'[First Transaction Date]<MIN('Date Table'[Date].[Date])
)
Hi. This is not working unfortunately.
@Kiki ok, no worries 🙂 will get there.
Can you maybe share the file / sample of the file with fake data and explain there in a text box the desired output you want to get.
Hi,
I hope this works?
Transaction Table | |||
Reference | Transaction Date | First Transaction Date | Last Date |
123 | 01/01/2019 | 01/01/2019 | 01/03/2022 |
123 | 01/05/2020 | 01/01/2019 | 01/03/2022 |
123 | 01/02/2022 | 01/01/2019 | 01/03/2022 |
145 | 01/02/2021 | 01/02/2021 | 01/03/2022 |
145 | 01/03/2022 | 01/02/2021 | 01/03/2022 |
167 | 01/03/2018 | 01/03/2018 | |
167 | 01/01/2022 | 01/03/2018 | |
187 | 01/01/2017 | 01/01/2017 | |
187 | 15/02/2022 | 01/01/2017 |
CALCULATE(DISTINCTCOUNT('Transaction Table'[Rreference]),
FILTER('Transaction Table','Transaction Table'[First Transaction Date]<MIN('Date Table'[Date].[Date])
&&OR('Transaction Table'[Last Transaction Date]>=MIN('Date Table'[Date].[Date]),ISBLANK('Transaction Table'[Last Transaction Date])))
I am expecting the count to be 4 i.e. 123, 145,167 & 187 because all of these references have a First Transaction Date of before the 1st of February 2022, and a Last Transaction Date of either after the 1st of February 2022 or blank.
However, I am getting a count of 2 i.e. 123 & 187, and I think this because these references have a Transaction Date within the month of February 2022 i.e. the selected date in the slicer.
User | Count |
---|---|
101 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
98 | |
97 | |
73 | |
72 |