Aging on open tickets but remove weekends

[ahxl]

Hi all

I have a column which has dates 

Issue Number: 1

Date: 28/08/2017

I need to have it calculate from this date till today the number of days passed, EXCLUDING Saturday and Sunday.

To make things more complicated, If I could add in a condition to just display the dates that are within 5 days or X amount of days from the original date that would be good as well.

What I would like in the end is:

Number of Issues 5 days Open : (COUNT) 

Number of Issues 10 days Open: (COUNT).

I have tried using the DATEDIFF measure but it includes weekends.

Thank you 

AHXL

[Anonymous]

Hi @ahxl

Try the following

1. Create a meassure to find the number of Saturdays and Sundays between the date in the table and today.

SatSuns =
Countrows (Filter (
         CALENDAR(Min(Yourtable[Datecolumn]),TODAY() ),WEEKDAY([Date],2) > 5)
                     )

What this does gets a table of Sundays and Saturdays between the dates and counts the same.

2. To find the age create a column

  Age = DATEDIFF(Min(Yourtable[Datecolumn]),TODAY(),DAY) + 1 - [SatSuns]

3. Now Plot a bar graph with Age as x-axis and CountofIssueNumber as Values.

If this works for you please accept this as a solution and also give Kudos.

Cheers

CheenuSing

[ahxl]

Hi Cheenu

Thanks for the reply, however I am still having issues.

I had to create your first formula as a Calculated column, as for each row in my Date Field I need to find out the count of Saturdays and Sundays. I tried craeting a calculated measure but this did not work. The formula I used was 

CALCSATSUNONLY = Countrows(Filter(Calendar(Min('CFS'[Responded on]),TODAY()),WEEKDAY('CFS'[Responded on],2)>5))

The column 'Responded on' Is in a date format. 

Then I created a calculated column for the ageing.

AGING = DATEDIFF(Min('CFS'[Responded on].[Day]),TODAY(),DAY) + 1 -[CALCSATSUNONLY]

The same values were returned for all rows. Why is this?

Some other points:

• I require in DAYS the aging of each of the rows to todays date. Do i need to add in Responded on.DAYS or Responded on.DATE
• I was unable to craete your first formula as a calculated measure, there was numerous syntax errors that occured....

Thanks

AHXL

[Anonymous]

Hi @ahxl

Please share your pbix or sample data in one drive or google drive and share the link here to check.

Cheers

CheenuSing