create slicer and put multiple years on a chart that calculates the open, closed and residual tasks

[remi]

Hello all,

Can you please let me know if there's a way to create a chart of open, closed and residual issues to which you can apply additional slicers - like the type of work/ service and that runs over different years with a monthly/weekly frequency?

This is what I've done with the help from https://community.powerbi.com/t5/Desktop/dax-how-to-count-open-tickets/m-p/100614#M42189

Buth, unfortunately, the solution given is hard to adapt to include other categories to slice it further for e.g see how many issues were GSM, UMTS, LTE from the total no (below chart), and also it doesn't account for the changing of the year ( it works very good within a year ...)

Thank you

[v-yulgu-msft]

Hi @remi,

I am not very clear about your requirement. Is above image you desired output? What slicer you want to apply to? Please illustrate the results displayed in above image with more information. Also, please post your sample data, it's hard to image your table structrue and detailed data.

Regards,

Yuliana Gu

[remi]

Hello @v-yulgu-msft,

Thank you for getting back to me!

The table is something like this:

TT Ref           Tech                          Open Date   Closed Date

TT05	LTE & UMTS & GSM	1/10/2016	21/10/2016
TT03	UMTS & GSM	10/10/2016	12/10/2016
TT06	GSM	10/10/2016	12/10/2016
TT07	UMTS	10/10/2016	1/1/2017
TT01	UMTS	1/1/2017	2/1/2017
TT02	GSM	2/1/2017	5/1/2017
TT04	LTE	12/12/2017	19/12/2017
TT08	LTE	1/1/2018	12/1/2018
TT09	LTE & UMTS	1/1/2018	21/1/2018

Already have a DAX for making the desired output on months, but can't figure out how to have multiple years and also have this easy to use output - Now the values rotate from January to December and adding up the values from multiple years.

Also was looking into how to slice the output (the above table has 6 categories: GSM, UMTS, LTE, UMTS & GSM, LTE & GSM, LTE & UMTS & GSM ) and want maybe to have a table for each of them if it's not possible to slice them so need to filter the data before outputting the table.

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

 Thank you and looking forward to your input.