Problem with DATESINPERIOD

[Anonymous]

I have created a DAX measure that repeatedly fails to return any results. I have used the same DAX code previously for another measure, and it worked correctly. The only differences are the tables being used. All tables are correctly related, and the date fields are in the same format.

Another difference is that in the current measure, I am attempting to use two tables. However, even when I simplify the measure to use only one table (e.g., only the 'CORRECT' table), it still does not work. There are indeed records that meet the three filters ('Taak afgerond' = "nee", 'Vandaag is na streefeinddatum taak' = "ja", and 'Zaak actief' = "ja"), so the issue seems to lie with the DATESINPERIOD function.

Can anyone see what the problem is?
This is the DAX code:

%openstaande taken_maand =
VAR EersteDatumCorrect =
CALCULATE(
MIN('zaken-taak-team CORRECT'[taak.begindatum]),
NOT(ISBLANK('zaken-taak-team CORRECT'[taak.begindatum]))
)//Enkel gebruikt om de eerste datum ontvangst zaaksysteem te bepalen die niet NULL is. Wil de NULL values echter niet uitsluiten in measure zelf

VAR ZakenIncompleetCorrect =
CALCULATE(
COUNTROWS('zaken-taak-team CORRECT'),
'zaken-taak-team CORRECT'[Taak afgerond] = "nee",
'zaken-taak-team CORRECT'[Vandaag is na streefeinddatum taak] = "ja",
'zaken-taak-team CORRECT'[Zaak actief] = "ja",
DATESINPERIOD(
dimDate[Date],
EersteDatumCorrect,
1,
MONTH
)
)

VAR TotaalIncompletezakenCorrect =
CALCULATE(
COUNTROWS('zaken-taak-team CORRECT'),
DATESINPERIOD(
dimDate[Date],
EersteDatumCorrect,
1,
MONTH
)
)

VAR EersteDatumFout =
CALCULATE(
MIN('zaken-taak-team-medewerker FOUT'[taak.begindatum]),
NOT(ISBLANK('zaken-taak-team-medewerker FOUT'[taak.begindatum]))
)

VAR ZakenIncompleetFout =
CALCULATE(
COUNTROWS('zaken-taak-team-medewerker FOUT'),
'zaken-taak-team-medewerker FOUT'[Taak afgerond] = "nee",
'zaken-taak-team-medewerker FOUT'[Vandaag is na streefeinddatum taak] = "ja",
'zaken-taak-team-medewerker FOUT'[Zaak actief] = "ja",
DATESINPERIOD(
dimDate[Date],
EersteDatumFout,
1,
MONTH
)
)

VAR TotaalIncompletezakenFout =
CALCULATE(
COUNTROWS('zaken-taak-team-medewerker FOUT'),
DATESINPERIOD(
dimDate[Date],
EersteDatumFout,
1,
MONTH
)
)

RETURN
DIVIDE(ZakenIncompleetCorrect, TotaalIncompletezakenCorrect, 0) +
DIVIDE(ZakenIncompleetFout, TotaalIncompletezakenFout, 0)

[technolog]

What to do depends on what you actually want.

1. If you want the ratio for the month on the axis or from a slicer, do not force your own date window. Let the date context from the report do the filtering, and only add the task status filters. Also do not add two percentages. Add the numerators, add the denominators, then divide once.

Example measure with the current month coming from the visual

Percent open tasks month
VAR numCorrect =
CALCULATE(
COUNTROWS( 'Correct' ),
'Correct'[Taak afgerond] = "nee",
'Correct'[Vandaag is na streefeinddatum taak] = "ja",
'Correct'[Zaak actief] = "ja"
)
VAR numFout =
CALCULATE(
COUNTROWS( 'Fout' ),
'Fout'[Taak afgerond] = "nee",
'Fout'[Vandaag is na streefeinddatum taak] = "ja",
'Fout'[Zaak actief] = "ja"
)
VAR denCorrect = COUNTROWS( 'Correct' )
VAR denFout = COUNTROWS( 'Fout' )
RETURN DIVIDE( numCorrect + numFout, denCorrect + denFout )

Replace the placeholder table names with yours. This version relies on the existing date context, so it will work on a matrix by month, with a month slicer, and inside any other date filter.

2. If you need to lock the calculation to a single explicit month based on the current selection, build that month window with start and end dates and use DATESBETWEEN. No negative numbers required.

Percent open tasks for selected month
VAR endDate = MAX( dimDate[Date] )
VAR startDate = DATE( YEAR( endDate ), MONTH( endDate ), 1 )

VAR numCorrect =
CALCULATE(
COUNTROWS( 'Correct' ),
'Correct'[Taak afgerond] = "nee",
'Correct'[Vandaag is na streefeinddatum taak] = "ja",
'Correct'[Zaak actief] = "ja",
DATESBETWEEN( dimDate[Date], startDate, endDate )
)
VAR numFout =
CALCULATE(
COUNTROWS( 'Fout' ),
'Fout'[Taak afgerond] = "nee",
'Fout'[Vandaag is na streefeinddatum taak] = "ja",
'Fout'[Zaak actief] = "ja",
DATESBETWEEN( dimDate[Date], startDate, endDate )
)
VAR denCorrect =
CALCULATE(
COUNTROWS( 'Correct' ),
DATESBETWEEN( dimDate[Date], startDate, endDate )
)
VAR denFout =
CALCULATE(
COUNTROWS( 'Fout' ),
DATESBETWEEN( dimDate[Date], startDate, endDate )
)
RETURN DIVIDE( numCorrect + numFout, denCorrect + denFout )

Three checks that prevent silent blanks

1. The date table must fully cover all task dates. Mark it as a date table. If the first task date is earlier than the first date in the date table, any window that starts there will be empty.

2. The relationships from the date table to both fact tables must be active on the same date columns that your visuals use. If a relationship is inactive, wrap each CALCULATE with USERELATIONSHIP.

3. When you place dimDate[Date] and a simple COUNTROWS from each table into a table visual, changing a date slicer should change both counts. If not, fix relationships first.

[Anonymous]

Hi @Anonymous 

I read the code you provided carefully, and i think the DATESINPERIOD() should work well.

I speculate that there are something else affect the outcome.

Please check if there are some data being filtered, if possible, please share your data(exclude sensitive data), or create some sample data, so that we can help you better.

You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.