Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello All,
I have a report page with a page of last 30 days. The bars are as is: Tickets Created (blue), Tickets Resolved (orange), and Tickets Open (purple). I originally had a running total for the last 30 days, but that is not what is exactly wanted. I need to get the total tickets open at the end of day for the entire dataset. As you can see my purple bar is the same across all the days. For example if the total open tickets as of June 7 was 50 and at the end of the day June 8 there were 5 more open tickets then the total for June 8 would be 55.
Here is the formula I'm using:
Total Open Tickets =
CALCULATE(DISTINCTCOUNT(DataTable[TicketId]),
ISBLANK(Data[ResolvedDate]),
ALL(CalendarTable[Date]))
Any help would be greatly appreciated.
Thanks!
Solved! Go to Solution.
Hi @nleuck ,
I forgot to add the ALLSELECTED() function.
Try this again.
And maybe my .pbix file can give you some help.
Total Open Tickets =
CALCULATE(
DISTINCTCOUNT( DataTable[TicketId] ),
//Filter out the resolved tickets
FILTER(
Data,
Data[ResolvedDate] = BLANK()
),
//Cumulative calculation
FILTER(
ALLSELECTED(CalendarTable),
CalendarTable[Date] <= MAX( CalendarTable[Date] )
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @nleuck ,
Has your problem been solved?
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @nleuck ,
Please refer to the formula, you shouldn't use ALL(CalendarTable[Date]) function. According to my understanding, you need to accumulate tickets according to the calendar date.
Total Open Tickets =
CALCULATE(
DISTINCTCOUNT( DataTable[TicketId] ),
//Filter out the resolved tickets
FILTER(
Data,
Data[ResolvedDate] <> BLANK()
),
//Cumulative calculation
FILTER(
CalendarTable,
CalendarTable[Date] <= MAX( CalendarTable[Date] )
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
First, I had to make a change to your formula. Data[ResolvedDate] <> Blank() needs to be Data[Resolved Date] = Blank(), because I want to find the total open tickets.
Even when I do that this formula gives me the same results as mine. Currently it's showing that we have a total of 52 open tickets, across everyday of the last 30 days. On June 8th the total was 49 and on June 9th there were 3 more open tickets so now the total is 52.
Hi @nleuck ,
I forgot to add the ALLSELECTED() function.
Try this again.
And maybe my .pbix file can give you some help.
Total Open Tickets =
CALCULATE(
DISTINCTCOUNT( DataTable[TicketId] ),
//Filter out the resolved tickets
FILTER(
Data,
Data[ResolvedDate] = BLANK()
),
//Cumulative calculation
FILTER(
ALLSELECTED(CalendarTable),
CalendarTable[Date] <= MAX( CalendarTable[Date] )
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
206 | |
90 | |
61 | |
59 | |
57 |