topic Total DISTINCTCOUNT() also for BLANKS() with SAMEPERIODLASTYEAR() calculations in DAX Commands and Tips

Total DISTINCTCOUNT() also for BLANKS() with SAMEPERIODLASTYEAR() calculations

denxx34 — Tue, 04 Jun 2024 07:31:08 GMT

Hello,

I need help with the following DAX scenario.

Data context: We buy items from vendors.

Question: Comparison of previous year with total number of suppliers.

Problem: Number of suppliers is "wrong" because only the selected year (2023) is calculated [DISTINCTCOUNT()], but I need the DISTINCTCOUNT() for all suppliers. Even if we didn't buy any of these in 2023. I need all of them from the previous year and the year before.

(Description see screenshot)

For 2023 > not #Vendors=3 > #Vendors=5

Any ideas on how to overcome this challenge?

Thanks in advance!

Data

fact

Vendor	Date	Amount	Item
A	15.01.2023	10	abc
B	15.07.2023	5	abc
C	15.08.2023	3	def
D	15.01.2022	6	abc
A	15.07.2022	4	abc
A	15.08.2022	2	def
B	20.01.2022	7	abc
E	15.01.2021	6	abc
A	15.07.2021	4	abc
A	15.08.2021	2	def

dimVendor

Vendor	VendorName
A	Aaa
B	Bbb
C	Ccc
D	Ddd
E	Eee

calendar =

FILTER (

CALENDARAUTO ( 3 ),

YEAR ( [Date] ) >= 2020

)

dimItem

Item	ItemName
abc	AbeCe
def	DeEef

Re: Total DISTINCTCOUNT() also for BLANKS() with SAMEPERIODLASTYEAR() calculations

Moetazzahran — Tue, 04 Jun 2024 10:47:54 GMT

Hello,
If I understand your problem correctly,

You can use two solutions.
1st solution is to remove the interaction between (The Date slicer and Fact)and the Vendor KPI card

2nd solution is to use an All() and Calculate in your vendor measure.

Please let me know if it works.

Thank you in advance.

Re: Total DISTINCTCOUNT() also for BLANKS() with SAMEPERIODLASTYEAR() calculations

xifeng_L — Tue, 04 Jun 2024 11:14:00 GMT

Moetazzahran You're counting all vendors this way, whereas the questioner should be needing the number of vendors based on the number of years less than or equal to the year selected by the slicer.

denxx34 You can try below measure.

#vendor = CALCULATE(DISTINCT('Fact'[Vendor]),'Calendar'[Date]<=MAX('Calendar'[Date]))

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~