Forum Discussion
Helper III5 days compare report
Hello everyone,
I have table with column Ticket ( A, B, C, D), Created_Date, Status (New, Open, Done).
I need to count the Ticket open (from created date to today) for > 5 days and > 15 days.
I create the Day_Count column to get > 5 days and > 15 days
What I am trying to do is to compare the latest day (count all open ticket until latest day) with 5 days ago (Count all open ticket until 5 days ago).
My question is:
1. How can I compare the latest day with 5 days ago and show these 2 days in the visual only.
2. How can show sum of all ticket until lastest day and 5 day ago.
Thank you so much!
Hi Harry_Tran ,
According to your description, I create this data：
Here are the steps you can follow：
1. Create calculated column to find day_ Count
Day_Count = var _states = IF('Table'[States]="Complied",BLANK(),[Create Date]) var _days=DATEDIFF(_states,TODAY(),DAY) return SWITCH( TRUE(), _days<=5&&_days<>BLANK(),"<=5", _days>5 && _days<=15&&_days<>BLANK(),">5 && <=15", _days>15 && _days<=30&&_days<>BLANK(),">15 && <=30", _days>30&&_days<>BLANK(),">30")Result:
2. Create measure displays the date 5 days ago and the latest date, and compares the counts of the two
Five_days_ago = CALCULATE(MAX('Table'[Create Date]),FILTER(ALL('Table'),'Table'[Create Date]=TODAY()-5))recently = CALCULATE(MAX('Table'[Create Date]),FILTER(ALL('Table'),'Table'[Create Date]<=MAX('Table'[Create Date])))Five_days_ago_count = var _days=CALCULATE(COUNT('Table'[Ticket]),FILTER(ALL('Table'),'Table'[Create Date]>=TODAY()-5 &&'Table'[Create Date]<=TODAY())) return _daysRecently_count = var _recently=CALCULATE(MAX('Table'[Create Date]),FILTER(ALL('Table'),'Table'[Create Date]<=MAX('Table'[Create Date]))) var _count=CALCULATE(COUNT('Table'[Ticket]),FILTER(ALL('Table'),'Table'[Create Date]=_recently&&'Table'[States]<>"Complied")) return _countcompara = IF([Five_days_ago_count]>[Recently_count],"five > recently","five <recently")Result:
3. Create measure,show sum of all ticket until lastest day and 5 day ago.
all = [Five_days_ago_count]+[Recently_count]Result:
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
Harry_Tran , first of important thing is how are you getting the open tickets. Based on that there can be a solution.
But to me, it seems like a dynamic Segmentation problem
https://www.youtube.com/watch?v=CuczXPj0N-k
Open days should be based on slicer day a formula with similar approch in here - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
of
datediff(Max(Table[OpenDate]), Selectedvalues(Date[Date]), Day) //or today, Filter of open flag.
You need independent table for bucketing a measure and you need to all new set of measures that follow this bucket.
Hi Harry_Tran ,
According to your description, I create this data：
Here are the steps you can follow：
1. Create calculated column to find day_ Count
Day_Count = var _states = IF('Table'[States]="Complied",BLANK(),[Create Date]) var _days=DATEDIFF(_states,TODAY(),DAY) return SWITCH( TRUE(), _days<=5&&_days<>BLANK(),"<=5", _days>5 && _days<=15&&_days<>BLANK(),">5 && <=15", _days>15 && _days<=30&&_days<>BLANK(),">15 && <=30", _days>30&&_days<>BLANK(),">30")Result:
2. Create measure displays the date 5 days ago and the latest date, and compares the counts of the two
Five_days_ago = CALCULATE(MAX('Table'[Create Date]),FILTER(ALL('Table'),'Table'[Create Date]=TODAY()-5))recently = CALCULATE(MAX('Table'[Create Date]),FILTER(ALL('Table'),'Table'[Create Date]<=MAX('Table'[Create Date])))Five_days_ago_count = var _days=CALCULATE(COUNT('Table'[Ticket]),FILTER(ALL('Table'),'Table'[Create Date]>=TODAY()-5 &&'Table'[Create Date]<=TODAY())) return _daysRecently_count = var _recently=CALCULATE(MAX('Table'[Create Date]),FILTER(ALL('Table'),'Table'[Create Date]<=MAX('Table'[Create Date]))) var _count=CALCULATE(COUNT('Table'[Ticket]),FILTER(ALL('Table'),'Table'[Create Date]=_recently&&'Table'[States]<>"Complied")) return _countcompara = IF([Five_days_ago_count]>[Recently_count],"five > recently","five <recently")Result:
3. Create measure,show sum of all ticket until lastest day and 5 day ago.
all = [Five_days_ago_count]+[Recently_count]Result:
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
