Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ahxl
Regular Visitor

Aging on open tickets but remove weekends

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

3 REPLIES 3
CheenuSing
Community Champion
Community Champion

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

 

 

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

 

 

CheenuSing
Community Champion
Community Champion

Hi @ahxl

 

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

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.