Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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))
does anyone know how to achieve the same for data which is for multiple years. i have date column in YYYYMM format.
This is great though I am wondering whether it's possible to do this by YEAR & MONTH?
I need a rolling last 1 year that can be filtered by date.
Hi @kiranrcse,
You can refer to below formula to get the summary table about tickets:
Summary Table =
SELECTCOLUMNS(
ADDCOLUMNS(FILTER(DISTINCT(SELECTCOLUMNS(UNION(VALUES(Sheet1[Closed Date].[Month]),VALUES(Sheet1[Opened date].[Month])),"Month",[Closed Date].[Month])),[Month]<>BLANK()),
"Open",COUNTAX(FILTER(ALL(Sheet1),[Opened date].[Month]=EARLIER([Month])),[SR]),
"Close",COUNTAX(FILTER(ALL(Sheet1),[Closed Date].[Month]=EARLIER([Month])),[SR])),
"Month",[Month],
"Open",if([Open]<>BLANK(),[Open],0),
"Close",if([Close]<>BLANK(),[Close],0),
"Total",if([Close]+[Open]<>BLANK(),[Open]+[Close],0))
Notice, above formula works when year of date fields have the same value.
Regards,
Xiaoxin Sheng
Hello @v-shex-msft,
Quick question how will the DAX need to be changed to account also for the year.
Thank you in advance!
Hello @v-shex-msft,
Quick question how will the DAX need to be changed to account also for the year.
Thank you in advance!
Thanks @v-shex-msft for the reply.
In the total column I am looking for all tickets which might be active in that month.
For example: For Month of April 3 new tickes have been opened, 1 ticket from Jan and 1 ticket from March are also still active/open.
The total count for the month of April is 5.
Is there a way to get that.
Hi @kiranrcse,
According to your description, I modified the formula, you can try to use below formula if it suitable for your requirement.
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))
Regards,
Xiaoxin Sheng
Hello Xiaoxin,
In my case there are one more column is over there which called Service content three different service as given below;
And I need to create Summary Table like:
Could you please help me with this output?
I had tried but still facing issue with Service wise sort out. I'm new into Power BI technology.
Kind Regards,
Harshal Kulkarni
hi @v-shex-msft,
Do you think you can help me with the files for this formula, I'm trying to go thru it but right now I've stopped at [SR].
Looking forward to your help.
Kind regards
Hello, @v-shex-msft and @kiranrcse,
Hope both of you is doing well.
Tried again to use the code shared, but now I run into the following alarm:
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Summary Table 3 =
SELECTCOLUMNS(ADDCOLUMNS(FILTER(DISTINCT(SELECTCOLUMNS(UNION(VALUES('Sheet1'[Closed Date].[MonthNo]),VALUES('Sheet1'[Opened date].[MonthNo])),"Month",'Sheet1'[Closed Date].[MonthNo])),[Month]<>BLANK()),
"Open",COUNTAX(FILTER(ALL('Sheet1'),'Sheet1'[Opened date].[MonthNo]=EARLIER([Month])||AND([Opened date].[MonthNo]=EARLIER([Month]),'Sheet1'[Closed Date]>MAX(Sheet1[Opened date]))),'Sheet1'[SR]),
"Close",COUNTAX(FILTER(ALL('Sheet1'),[Closed Date].[MonthNo]=EARLIER([Month])),'Sheet1'[SR]),
"Total",COUNTAX(FILTER(ALL('Sheet1'),'Sheet1'[Opened date].[MonthNo]<=EARLIER([Month])&&OR('Sheet1'[Closed Date].[MonthNo]>=EARLIER([Month]),'Sheet1'[Closed Date]=BLANK())),'Sheet1'[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))
Can you tell me where it fails?
Kind regards.
HI @v-shex-msft,
Finally understood it, thank you!
You have to do Modeling -> New Table
I'm new to this and didn't understand it.
Now all good, thank you!
Hi @remi,
My formula is works on calculate table, if you drag it to measure or calculate column, it will alert for that error message.
Regards,
Xiaoxin Sheng
Hi @v-shex-msft,
Thank you for your previous replies.
Can you tell me how do you change the dax to include multiple years and how would you introduce a category of tickets so that you can filter on those.
Thank you in advance.
Hi @v-shex-msft,
Thanks for getting back to me! Not sure how you do that, I've tried to do the small example that you've provided me with and that how I've got the error.
Reason for me requesting an example file was that thought that would speed the result. Will you be able to help with it?
Kind regards,
Remi Nastase
Hi @v-shex-msft,
Thanks for getting back to me! Not sure how you do that, I've tried to do the small example that you've provided me with and that how I've got the error.
Reason for me requesting an example file was that thought that would speed the result. Will you be able to help with it?
Kind regards,
Remi Nastase
Hello @v-shex-msft,
Thank you for getting back to me!
Do you think you can help me with the pbix file to it... not sure what you mean by: "if you drag it to measure or calculate column, it will alert for that error message."
Thank you.
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))
I have an excel file and have the first quarterly, Half Yearly , Third Quarter, and annual revenue of multiple years.
I want to display only latest data on cards. But that didn't happened when i choose column in into field area that have specific options of sum, avg, standard deviation, min , max etc
How can i display actual cell data only specific cell data not whole column.
Plz anyone guide me
Hi, Can you please help to pull the count of resolved tickets which are created in different year and monthly basis.
can we get formula to use for differenet years please.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |