Comparison of last working day for previous months

[Anonymous]

Struggling to write the DAX to calculate the below, any help would be much appreciated. 

I currently have a set of data which i'd like to track the change between last working days for the previous 2 months. I'm able to calculate the differences however I'm struggling to return the last working days for previous months.

These should return 29th Oct & 30th November

[v-yalanwu-msft · Accepted solution]

Hi， @Anonymous ；

You could create a measure such as:

Current Date1 =
VAR _eomon =
    EOMONTH ( MAX ( [Date] ), -1 )
RETURN CALCULATE (MAX ( [Date] ),
        FILTER (ALL ( carlendar ),
            WEEKDAY ( [Date], 2 )IN { 1, 2, 3, 4, 5 }
                    && YEAR ( [Date] ) = YEAR ( _eomon )
                    && MONTH ( [Date] ) = MONTH ( _eomon )))

Previous Date1 = 
VAR _eomon =
    EOMONTH ( MAX ( [Date] ), -2 )
RETURN CALCULATE ( MAX ( [Date] ),
        FILTER (ALL ( carlendar ),
            WEEKDAY ( [Date], 2 ) IN { 1, 2, 3, 4, 5 }
                    && YEAR ( [Date] ) = YEAR ( _eomon )
                    && MONTH ( [Date] ) = MONTH ( _eomon )))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Amitchandak, 

Thanks for the response. 

Here is a screenshot of my date table. 

In essence I want to display a comparison of the INC Count only for the last working day of the previous two months.

e.g as today is December 8th, I'd need to display the dates for 30 Nov & 29 Oct. If either of these dates would fall on the weekend then it would return the date on friday

I was was trying to write two measures. 

Current Date = should return 30th Nov

Previous Date = should return 29th Oct

[VahidDM]

Hi @Anonymous 

Try this:

Measure =
VAR _T =
    TODAY()
VAR _FT =
    DATE( YEAR( _T ), MONTH( _T ), 1 )
VAR _1FD =
    DATE( YEAR( _FT - 1 ), MONTH( _FT - 1 ), 1 )
VAR _2FD =
    DATE( YEAR( _1FD - 1 ), MONTH( _1FD - 1 ), 1 )
VAR _1ED =
    CALCULATE(
        MAX( Date[Date] ),
        FILTER(
            ALL( Date ),
            Date[Year] = YEAR( _1FD )
                && Date[Month] = MONTH( _1FD )
                && Date[is Weekend] = 0
        )
    )
VAR _2ED =
    CALCULATE(
        MAX( Date[Date] ),
        FILTER(
            ALL( Date ),
            Date[Year] = YEAR( _2FD )
                && Date[Month] = MONTH( _2FD )
                && Date[is Weekend] = 0
        )
    )
RETURN
    CALCULATE(
        SUM( Table[Value] ),
        FILTER( ALL( 'Date' ), Date[Date] = _1ED || Date[Date] = _2ED )
    )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[amitchandak]

@Anonymous ,

A measure like

calculate(lastnonblankvalue('Date'[Date], sum(Table[Value]) ) , filter(allselected('Date'), Date[Month Year] = max( Date[Month Year] )))

Assumed you are using date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.