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 All,
I'm struggling to get my head around this. I have a ticketing system, and what I need to try and work out is how many tickets were open at the end of each month.
Tickets are calculated by a distinctcount of ticket number
Each ticket has a create date
Each tickets has a complete date (or blank if not completed)
I have a separate table for Date which has relationships with completed and created times.
I am trying to produce a graph where the axis is the date, and then display tickets that were open in each month
I need something along the lines of
Calculate(DistinctCount(Tasknumber),createtime<=month and complete time is blank or >month)
I cant seem to get this working
Thanks
Solved! Go to Solution.
I'm really sorry.
After raising this topic it showed me reccomendations.
That one above nailed exactly what I needed.
Kudos
Refer to the article on the same line: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin
I'm really sorry.
After raising this topic it showed me reccomendations.
That one above nailed exactly what I needed.
Kudos
Hey @jondurbridge ,
my data model looks like:
There are relationship between
It's essential to know, that the invoice date key corresponds to your created time and the delivery date key corresponds to your completed time.
Here is the measure that counts the open sales keys:
no of open saleskeys =
var datemax = CALCULATE(MAX('Dimension Date'[Date]))
return
CALCULATE(
SUMX(
FILTER(
'Fact Sale'
,
('Fact Sale'[Invoice Date Key] <= datemax && ISBLANK('Fact Sale'[Delivery Date Key]))
||
('Fact Sale'[Invoice Date Key] <= datemax && 'Fact Sale'[Delivery Date Key] > datemax)
)
, 1
)
, ALL('dimension Date')
, CROSSFILTER('Dimension Date'[Date] , 'Fact Sale'[Delivery Date Key] , None)
, CROSSFILTER('Dimension Date'[Date] , 'Fact Sale'[Invoice Date Key] , None)
)
Hopefully, this provides what you are looking for. If not please consider to provide a pbix that contains sample data and reflects your data model. Upload the file to onedrive or dropbox and share the link.
Regards,
Tom
Alberto Ferrari has a video with a use case for Open Orders that is similar to your needs.
See this video starting at minute 50
https://www.sqlbi.com/tv/dax-optimization-examples/
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |