Skip to main content
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

View discussion in a popup

Replying to:
technolog
Super User

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.

  1. 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.

View solution in original post