Subtract days in month by absence days

[Anonymous]

Month and Year	PERSON ID 135	PERSON ID 721	PERSON ID 1003	Total
January 2019			(31-4)	27
February 2019	(28-6)			22
March 2019	(31-1)	(31-4)		26
June 2019			(30-23)	7
July 2019			(31-20)	11
August 2019		(31-2)		29
September 2019			(30-4)	26

I've created a cross join table to count the difference between two dates and present them in a month format (above):

Step1. Calendar date = CALENDAR(MIN('Table'[DATE_START]),MAX('Table'[DATE_END]))

Step 2. Cross Join =

FILTER (

CROSSJOIN ( 'Table', 'Calendar date' ),

[Date] >= 'Table'[DATE_START]-1

&& [Date] < 'Table'[DATE_END]

)

Step 3. Month Name = 'Cross Join'[Date].[Month]

Is there a way to subtract the number of days in each month by the absence figures I've found in each month? e.g. 31days in January - 6 absence days

Days in Month = DAY ( EOMONTH ( [Month and Year], 0 ))

File attached

https://www.dropbox.com/s/0lfr3dota46qi2p/Sample%20v1.pbix?dl=0

[amitchandak]

@Anonymous , refer my file , where I have distributed leave across days

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

I have not checked your file

[Anonymous]

If I have created a measure:

AbsentByDay = CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN('Table','DimDate'),'DimDate'[Date] >= 'Table'[DATE_START] && 'DimDate'[Date]<= 'Table'[DATE_END]),'Table'[PERSON_ID],'DimDate'[Date]),'DimDate'[Date]),FILTER(DimDate,DimDate[WorkingDays] <>BLANK()))

Is there a way to edit this measure to subtract days in month (e.g. 31days in August 2019 - 5 absences):

Days in Month = DAY ( EOMONTH ( [Month and Year], 0 ))

https://www.dropbox.com/s/8mvfco06tc9gioa/Sample1.pbix?dl=0

[v-yiruan-msft]

Hi @Anonymous ,

I created two measures as below to get the working days and applied the new measure [Measure 2] on the matrix to replace the measure [AbsentByDay]. You can find the details in the attachment.

Measure = 
VAR _lastday =
    DAY (
        EOMONTH (
            DATE ( LEFT ( MAX ( 'DimDate'[Month Year] ), 4 ), VALUE ( RIGHT ( MAX ( 'DimDate'[Month Year] ), 2 ) ), 1 ),
            0
        )
    )
RETURN
    IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday ) - [AbsentByDay]

Measure 2 = SUMX(VALUES('DimDate'[Month Year]),[Measure])

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.

[Anonymous]

This works great for full month figures. Thank you so much for this. Is there a way to apply just working days to this?

e.g. NOT WEEKDAY( 'DimDate'[Date] ) IN { 1,7 }

to

Measure =

VAR _lastday =

DAY (

EOMONTH (

DATE ( LEFT ( MAX ( 'DimDate'[Month Year] ), 4 ), VALUE ( RIGHT ( MAX ( 'DimDate'[Month Year] ), 2 ) ), 1 ),

0

)

)

RETURN

IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday ) - [AbsentByDay]

[Anonymous]

I have an almost 100% solution to this: 

Measure 3 =

VAR _Month =

EOMONTH (

DATE ( LEFT ( MAX ( 'DimDate'[Month Year] ), 4 ), VALUE ( RIGHT ( MAX ( 'DimDate'[Month Year] ), 2 ) ), 1 ),

0

)

VAR _lastday=

COUNTROWS(

FILTER(

CALENDAR( EDATE(_Month , -1)+1 , _Month ),

NOT( WEEKDAY( [Date] ) IN { 1,7 })

)

)

RETURN

IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday ) - [AbsentByDay]

However there are some values that are not calculating correctly, can anyone help?

https://www.dropbox.com/s/jrvtgfms7akm1fv/Sample2%28updated%29.pbix?dl=0

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I made upadate on the formula of Meaure 3, now it can get the correct result...

Measure 3 =
VAR _Month =
    EOMONTH (
        DATE ( LEFT ( MAX ( 'DimDate'[Month Year] ), 4 ), VALUE ( RIGHT ( MAX ( 'DimDate'[Month Year] ), 2 ) ), 1 ),
        0
    )
VAR _lastday =
    COUNTROWS (
        FILTER (
            'DimDate',
            'DimDate'[Date] >= DATE ( YEAR ( _Month ), MONTH ( _Month ), 1 )
                && 'DimDate'[Date] <= _Month
                && NOT ( WEEKDAY ( [Date] ) IN { 1, 7 } )
        )
    )
RETURN
    IF ( ISBLANK ( [AbsentByDay] ), BLANK (), _lastday ) - [AbsentByDay]

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.