Incorrect Measure total on a matrix if more than one rows

[Anonymous]

Hi Community,

I need help! I have this measure to calculated the no of days of leaves taken as following:

Leave days count: =

VAR startdate =

MAX ( 'Table'[leave start date] )

VAR enddate =

MAX ( 'Table'[leave end date] )

VAR calendartable =

FILTER ( Datedim, Datedim[Date] >= startdate && Datedim[Date] <= enddate )

return

COUNTROWS ( calendartable )

Issue is with measure total when there are more than one rows of leaves for an employee (Example Emp I in Nov):  The first part of the image is just displaying the counts based on leave start & leve end dates. Second part of the image is where there is issue with totals .

Sample pbi file: https://1drv.ms/u/s!Ag919_pO_UKrgSajygGEcFrESIsZ?e=Qy2F6c

Many thanks

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

Let me talk about the reason for the error first, because you did not put the start and end date columns in second matrix, so that the measure can only get the largest date.

So you need to define a context in the measure first, so that even if you don't put the start and end dates, it won't affect the result.

Like this:

Measure 2 = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', [Emp], 'Table'[leave start date], 'Table'[leave end date] ),
        "diff",
            COUNTROWS (
                FILTER (
                    Datedim,
                    Datedim[Date] >= EARLIER ( 'Table'[leave start date] )
                        && Datedim[Date] <= EARLIER ( 'Table'[leave end date] )
                )
            )
    ),
    [diff]
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[Anonymous]

@v-janeyg-msft Thank you for explaining the reason for the issue.

[amitchandak]

@Anonymous , Try a measure like

sumx('Table', datediff('Table'[leave start date] , 'Table'[leave end date], day))

[Anonymous]

@amitchandak  sorry that doesn't work for me.