Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi,
I have a db that contains tickets a ticket is created in a date and closed in an other,
the ticket is open if he dont have a date closed or he is between the two dates
the question is how in per exemple in 10/07/2016 i can know the exactly number
of tickets with DAX please ??
Solved! Go to Solution.
Here is a similar post for your reference.
http://community.powerbi.com/t5/Desktop/Trend-from-History-Table/m-p/200304#M88115
Given a table of Dates, how about a calculated column like this?
OpenTickets = CALCULATE(COUNTROWS(Tickets),FILTER(Tickets,(Tickets[create_ticket]<=[Dates] && ISBLANK(Tickets[close_ticket_date]) || (Tickets[create_ticket]<=[Dates] && Tickets[close_ticket_date]>=[Dates]))))
Easiest way would be to go to the data model and click on New Table and use the following formula:
DateTable = CALENDARAUTO()
Then, flag that column as a Date field versus Date/Time field.
Here is a similar post for your reference.
http://community.powerbi.com/t5/Desktop/Trend-from-History-Table/m-p/200304#M88115
@Greg_Deckler@v-chuncz-msf
Hi,
what i want really is without the extraction of filter tables, can make the count dynamic in other words the count change when i change a criteria in the filter pane i tested a lot of a possibilities; there is a way to do it without creating too much filter tables ??
france = CALCULATE (
COUNT ( emea_france[CallID] );
FILTER (
emea_france;
emea_france[NewColumn.RecvdDate] <= [Date]
&& (
emea_france[NewColumn.ClosedDate]> [Date]
|| ISBLANK ( emea_france[NewColumn.ClosedDate] )
)
)
)
I tried it but it gives me wrong numbers i think its due to the calendar auto date it begin from
1899
Given a table of Dates, how about a calculated column like this?
OpenTickets = CALCULATE(COUNTROWS(Tickets),FILTER(Tickets,(Tickets[create_ticket]<=[Dates] && ISBLANK(Tickets[close_ticket_date]) || (Tickets[create_ticket]<=[Dates] && Tickets[close_ticket_date]>=[Dates]))))
Hi Greg,
Attempting to use this to calculate open tickets in my data set - but keep getting the following error on my calendar date:
A single value for column 'date' in table 'calendar' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
My measure is:
@Greg_Deckler Hi Greg, I'm very much a beginner, but I thought I would try to apply this as well.
I would also like to show how many tickets are open on a specific date. I have a source of tickets (Query1) with a unique number in column 'No' and the fields 'Created_on' and 'Closed_on' with them both being data type 'Date'. Should I then add a column of:
i think its what i need but if you can help me im a beginner to create the table dates
Easiest way would be to go to the data model and click on New Table and use the following formula:
DateTable = CALENDARAUTO()
Then, flag that column as a Date field versus Date/Time field.
hi,
Table 3 = ADDCOLUMNS (
CALENDAR(MIN(CallLog[RecvdDate]);MAX(CallLog[RecvdDate]));
"Count"; CALCULATE (
COUNT (CallLog[CallID]);
FILTER (
CallLog;
CallLog[RecvdDate]<= [Date]
&& (
CallLog[ClosedDate] > [Date]
|| ISBLANK (CallLog[ClosedDate])
)
)
)
)
i did the function its working with the merge of two tables and selecting the values in the merge, what i want to do if its possible
is to count with just filters in the graphic panel if its possible ??
If you can supply sample data, I or someone else can supply a specific answer. Otherwise, the general process would be to count all of the tickets with an open date that is less than or equal to the reference date and do not have a close date. Something along the lines of:
CALCULATE(COUNT(Tickets[TicketID]),FILTER(Tickets,Tickets[OpenDate] <= DATE(2016,10,7) && ISBLANK(Tickets[CloseDate])))
okey sorry this is an example in excel
create_ticket | close_ticket_date | status |
10/02/2015 | 10/02/2015 | close |
10/02/2015 | 11/02/2015 | close |
11/02/2017 | 17/06/2017 | close |
20/05/2017 | open |
what i want in my graphe is to know that for example in the day 10/02/2015
i was having 2 open tickets and in the day 20/05/2017 2 also like i explained ??
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |