Count distinct occurrences in last month.

[Anonymous]

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.

[Jihwan_Kim · Accepted solution]

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
    )

[Anonymous]

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

[Jihwan_Kim]

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.