topic Count distinct occurrences in last month. in DAX Commands and Tips

Count distinct occurrences in last month.

Anonymous — Fri, 11 Nov 2022 08:40:00 GMT

I creating a scorecard for suppliers and try create 2 measures:

1. counting distinct occurance in last month

2 counting distinct occurances in last 6 months.

Dax formula will be similar just cant get it to work.

My table colums are:

Any help would be greatly appreciated.

Re: Count distinct occurrences in last month.

Jihwan_Kim — Fri, 11 Nov 2022 08:55:44 GMT

Hi,

I tried to create a sample pbix file like below.

If your datamodel does not have dim-date-column, try using some DAX functions like below.

Last six months distinct category count: = VAR _currentmonth = EOMONTH ( TODAY (), 0 ) VAR _startingmonth = EOMONTH ( TODAY (), -6 ) + 1 RETURN CALCULATE ( DISTINCTCOUNT ( Data[Category] ), Data[Date] <= _currentmonth, Data[Date] >= _startingmonth )

Re: Count distinct occurrences in last month.

Anonymous — Fri, 11 Nov 2022 09:09:37 GMT

This is perfect! Thank you.

Depending on the count I want to assign a score. Is is better to do it in the same measure or do you create seperate measre?

0 NCM = 100/100 (assigned score)

1 NCMs = 75/100

2 NCMs = 50/100

3+ NCMs = 0/100

Re: Count distinct occurrences in last month.

Jihwan_Kim — Sat, 12 Nov 2022 05:13:01 GMT

Hi,

Thank you for your feedback.

I think it depends on how you want to create a visualization.

If you want to show it one by one in a card visualization, you can separately create each measure.

If you want to show it by a bar chart, for instance, 0,1,2,+3 are the component of the axis and you have to create an axis table separately, and use one measure to show them as a bar chart.

I hope this helps.

Thanks.