Measure to return Days Past for current period/month from table as shown

[Route11]

Hi Experts

How would you write a measure to return the number of WorkingDaysPassed for Oct 18 from the table shown. I want to add the measure to a matrix table..

[v-shex-msft · Accepted solution]

Hi @Route11,

You can try to use below measure to calculate remain working day based on today function and table date column:

Remain Working days = 
IF (
    TODAY () > ENDOFMONTH ( 'Sample'[Date] ),
    0,
    IF (
        FORMAT ( TODAY (), "mm/yyyy" )
            = FORMAT ( ENDOFMONTH ( 'Sample'[Date] ), "mm/yyyy" ),
        COUNTROWS (
            FILTER (
                CALENDAR ( TODAY (), ENDOFMONTH ( 'Sample'[Date] ) ),
                NOT ( WEEKDAY ( [Date], 1 ) IN { 1, 7 } )
            )
        ),
        COUNTROWS (
            FILTER (
                CALENDAR ( STARTOFMONTH ( 'Sample'[Date] ), ENDOFMONTH ( 'Sample'[Date] ) ),
                NOT ( WEEKDAY ( [Date], 1 ) IN { 1, 7 } )
            )
        )
    )
)

Regards,

Xiaoxin Sheng