Trying to Count Number of Work Days in CURRENT Month

[RossP96]

Hi,

I have a measure which works out the remaining work days left in the current month. I need to then divide this number by the total work days in the current month to work out % of the month left.

The remaining work days formula is:

CALCULATE(SUM('Date'[Workday]),

DATESBETWEEN('Date'[Date],

NOW(),

EOMONTH(NOW(),0)))

I need to re-create this formula, but replace NOW() with the start date of the current month. I'm struggling to work out how to find the start date of the current month in dax?

I won't be using any date filters on the page so it needs to be based on the current date.

Thanks 

[Anonymous]

HI @RossP96 ,

You can try ot use following measure formula if it suitable for your requirement:

Measure =
VAR currDate =
    MAX ( Date[Date] )
RETURN
    CALCULATE (
        SUM ( 'Date'[Workday] ),
        FILTER (
            ALLSELECTED ( Date ),
            'Date'[Date]
                IN CALENDAR ( currDate, DATE ( YEAR ( currDate ), MONTH ( currDate ), 1 ) - 1 )
        )
    )

Notice: you can create a table visual with date fields and above measure.

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng

[jthomson · Accepted solution]

You're on the right line with EOMONTH, change the offset to -1 to get the last day of the previous month, then add 1