Forum Discussion

brokencornets's avatar
brokencornets
Helper IV
1 year ago
Solved

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 ( '...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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:

    1. Extend your Date table to include future dates, e.g., up to EOMONTH(TODAY(), 6), and ensure it's marked as a Date Table.
    2. In your visual, enable "Show items with no data" on the date axis to display all months.
    3. 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.

     

     

brokencornets's avatar
brokencornets
Helper IV
1 year ago

hi Pankaj

 

I appreciate your response, however it's bringing back the same results?

brokencornets's avatar
brokencornets
Helper IV
1 year ago

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))