Reply
Highlighted
Regular Visitor
Posts: 38
Registered: ‎08-06-2018
Accepted Solution

Retrieve Last 3 working days in each month

Hi All

 

How can retrieve last 3 working days in each month , please advice

 

Thanks in Advance

 

Regards

Jayanthan


Accepted Solutions
Super User
Posts: 763
Registered: ‎09-16-2018

Re: Retrieve Last 3 working days in each month

@jayanthan

 

I am guessing you want to add a column which returns True for the last 3 working days in a month so you can slice by them in your report. You can do it by adding first this column:

 

WorkDay = WEEKDAY( [Date], 2 ) < 6

and then this one:

 

LastThreeDaysInMonth = 
CONTAINS(
    CALCULATETABLE(
        TOPN( 3, 'Calendar', [Date], DESC ),
        ALLEXCEPT( 'Calendar', 'Calendar'[Month], 'Calendar'[Year] ),
        'Calendar'[WorkDay] = TRUE
    ),
    'Calendar'[Date],
    'Calendar'[Date]
)


Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!

View solution in original post


All Replies
Super User
Posts: 763
Registered: ‎09-16-2018

Re: Retrieve Last 3 working days in each month

Hi @jayanthan

 

do you have the date table already set up?



Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!

Regular Visitor
Posts: 38
Registered: ‎08-06-2018

Re: Retrieve Last 3 working days in each month

Hi @LivioLanzo

 

Yes , i have , please find the calendar pbix in below given link

 

https://www.dropbox.com/s/pxxc5523qv6b5hj/Calendar_File.pbix?dl=0

 

Regards

Jayanthan

Super User
Posts: 763
Registered: ‎09-16-2018

Re: Retrieve Last 3 working days in each month

@jayanthan

 

I am guessing you want to add a column which returns True for the last 3 working days in a month so you can slice by them in your report. You can do it by adding first this column:

 

WorkDay = WEEKDAY( [Date], 2 ) < 6

and then this one:

 

LastThreeDaysInMonth = 
CONTAINS(
    CALCULATETABLE(
        TOPN( 3, 'Calendar', [Date], DESC ),
        ALLEXCEPT( 'Calendar', 'Calendar'[Month], 'Calendar'[Year] ),
        'Calendar'[WorkDay] = TRUE
    ),
    'Calendar'[Date],
    'Calendar'[Date]
)


Did I answer your question? Mark my post as a solution!


Proud to be a Datanaut!

Regular Visitor
Posts: 38
Registered: ‎08-06-2018

Re: Retrieve Last 3 working days in each month

Hi LivioLanzo

 

Wow,It works, thank you so much for the support

 

Regards

Jayanthan