Counting how many times a row which is not blank appears within a certain period and create a ratio

[Clout]

Hello,

I want to create a DAX to check if the workers met the requiered count of measurements.

So they have to do for example one measurement per month and I want to check how many times they did the measurement within the month. After that I want to create a ratio between the total count of measurements and the requiered number of measurements. 

So when I put my filter from June to July, the count of measurements within the month is divided by 1. From June to November is divided by 6 and when I want to look at the whole year the count of measurements is divided by 12 and so on.

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Clout ，

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a date dimension table and create relationship with the fact table base on date field

2. Create a slicer base on the date field of Date table

3. Create a measure as below

Measure = 
VAR _mindate =
    MIN ( 'Date'[Date] )
VAR _maxdate =
    MAX ( 'Date'[Date] )
VAR _diffmonth =
    DATEDIFF ( _mindate, _maxdate, MONTH )
RETURN
    DIVIDE (
        CALCULATE ( DISTINCTCOUNT ( 'Table'[Measurement] ) ),
        IF ( _diffmonth <= 0, 1, _diffmonth )
    )

Best Regards

[amitchandak]

@Clout ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.