Forum Discussion
Calculating outstanding tickets
Help me out here guys. I have a measure that calculates outstanding tickets by month as per the below:
Outstanding =
CALCULATE (
COUNTROWS ( 'Tickets' ),
FILTER (
'Tickets',
( 'Tickets'[Date Reported] <=LASTDATE('Date'[Date])
&& ('Tickets'[Date Completed] > LASTDATE('Date'[Date])
)
)),REMOVEFILTERS()) +
CALCULATE (
COUNTROWS ( 'Tickets' ),
FILTER (
'Tickets',
( 'Tickets'[Date Reported]<=LASTDATE('Date'[Date])
&& (ISBLANK('Tickets'[Date Completed])
)
)),REMOVEFILTERS())
But for some reason even though I have data all the way to the end of April, it's not showing any data for April:
I know this will probably be something really obvious but I'm completely scratching my head on this one
Hi brokencornets ,
Thank you for reaching out to the Microsoft fabric community forum.
The issue you're facing where April doesn't appear despite having data is likely due to the visual not rendering months with no matching rows after filters are applied.
Here’s what we recommend:
- Extend your Date table to include future dates, e.g., up to EOMONTH(TODAY(), 6), and ensure it's marked as a Date Table.
- In your visual, enable "Show items with no data" on the date axis to display all months.
- Use the revised measure below to preserve date context and return accurate outstanding counts:
Outstanding = VAR ReferenceDate = MAX('Date'[Date]) RETURN CALCULATE ( COUNTROWS('Tickets'), FILTER ( ALL('Tickets'), 'Tickets'[Date Reported] <= ReferenceDate && ( ISBLANK('Tickets'[Date Completed]) || 'Tickets'[Date Completed] > ReferenceDate ) ) )This will ensure proper month-by-month reporting even if no tickets are active in certain periods.
I hope this will resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
7 Replies
Hello brokencornets
Try this measure
Outstanding=
VAR ReferenceDate = MAX('Date'[Date]) -- or LASTDATE
RETURN
CALCULATE (
COUNTROWS ( 'Tickets' ),
FILTER (
'Tickets',
'Tickets'[Date Reported] <= ReferenceDate
&& 'Tickets'[Date Completed] > ReferenceDate
)
)
+
CALCULATE (
COUNTROWS ( 'Tickets' ),
FILTER (
'Tickets',
'Tickets'[Date Reported] <= ReferenceDate
&& ISBLANK ( 'Tickets'[Date Completed] )
)
)
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
hi Pankaj
I appreciate your response, however it's bringing back the same results?
Okay so this is driving me INSANE now - it's also affecting another measure (Asset Count)
I've created a measure called 'Outstanding Max Date' which shows todays date and brings back correct values - but whenever I use the Date table instead it ends at March 2025
I'm guessing it must have something to do with relationships but then surely it would either bring back NO data or data for all months? What could have happened for it to stop in March?
I create my Date table as follows if that helps:
Date = ADDCOLUMNS ( CALENDAR (DATE(2016,1,1), TODAY()), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthYearShort", FORMAT ( EOMONTH([Date],-1)+1, "mmm YYYY" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "Weekday",if(and(weekday([Date])>1, weekday([Date])<7),1,0))
Hi brokencornets ,
Thank you for reaching out to the Microsoft fabric community forum.
The issue you're facing where April doesn't appear despite having data is likely due to the visual not rendering months with no matching rows after filters are applied.
Here’s what we recommend:
- Extend your Date table to include future dates, e.g., up to EOMONTH(TODAY(), 6), and ensure it's marked as a Date Table.
- In your visual, enable "Show items with no data" on the date axis to display all months.
- Use the revised measure below to preserve date context and return accurate outstanding counts:
Outstanding = VAR ReferenceDate = MAX('Date'[Date]) RETURN CALCULATE ( COUNTROWS('Tickets'), FILTER ( ALL('Tickets'), 'Tickets'[Date Reported] <= ReferenceDate && ( ISBLANK('Tickets'[Date Completed]) || 'Tickets'[Date Completed] > ReferenceDate ) ) )This will ensure proper month-by-month reporting even if no tickets are active in certain periods.
I hope this will resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.
Thankyou.
Hi brokencornets ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
Hi @brokencornets ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi brokencornets ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
