Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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:
Thanks
AHXL
Hi @ahxl
Please share your pbix or sample data in one drive or google drive and share the link here to check.
Cheers
CheenuSing
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |