Solved: Re: dax - how to count open tickets - Page 2

kiranrcse · ‎12-09-2016

For the same sample data, is there a way to get this output. I am looking for Total Active tickets during that month.

Expected report output:

	Total Active	Opened	Closed
Jan	2	2	0
Feb	3	1	2
Mar	2	1	0
Apr	5	3	0
May	6	1	2

Sample Data:

SR	Opened date	Closed Date
T1	1-Jan-16
T2	1-Jan-16	1-Feb-16
T3	2-Feb-16	4-Feb-16
T4	3-Mar-16
T5	4-Apr-16
T6	4-Apr-16
T7	4-Apr-16	1-May-16
T8	1-May-16	8-May-16

Thanks in Advance,

Kiran.

kiranrcse · ‎12-16-2016

Thanks @v-shex-msft. I made a small change to the code snippet you provided and it worked excellent.

I changed this :

Sheet1[Closed Date].[MonthNo]>=EARLIER([Month])

Summary Table = 
SELECTCOLUMNS(
    ADDCOLUMNS(FILTER(DISTINCT(SELECTCOLUMNS(UNION(VALUES(Sheet1[Closed Date].[MonthNo]),VALUES(Sheet1[Opened date].[MonthNo])),"Month",[Closed Date].[MonthNo])),[Month]<>BLANK()),
        "Open",COUNTAX(FILTER(ALL(Sheet1),[Opened date].[MonthNo]=EARLIER([Month])||AND([Opened date].[MonthNo]=EARLIER([Month]),Sheet1[Closed Date]>MAX(Sheet1[Opened date]))),[SR]),
        "Close",COUNTAX(FILTER(ALL(Sheet1),[Closed Date].[MonthNo]=EARLIER([Month])),[SR]),
        "Total",COUNTAX(FILTER(ALL(Sheet1),Sheet1[Opened date].[MonthNo]<=EARLIER([Month])&&OR(Sheet1[Closed Date].[MonthNo]>=EARLIER([Month]),Sheet1[Closed Date]=BLANK())),[SR])),
                "Month",FORMAT(DATE(2016,[Month],1),"MMMM"),
                "Close",if([Close]>0,[Close],0),
                "Open",if([Open]>0,[Open],0),
                "Total",if([Total]>0,[Total],0))

View solution in original post

remi · ‎12-14-2017

hello @kiranrcse,

can you help me with a .pbix example of what you've done?

I have some trouble understanding how to adapt the code to my data

https://community.powerbi.com/t5/Desktop/Calculate-the-remaining-tasks-and-percentage-of-the-complet...

kind regards

ImkeF · ‎12-12-2016

You also find a very nice solution to that problem here (especially if you run into performance problems): http://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

dax - how to count open tickets

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly