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 need to write a DAX for a measure that shows me 52 weeks high(max) and 52 weeks low(min) for ticket count by application.
I have attached screenshot for columns in the table and desired output.
Re-phrasing
I am trying to get this in matrix visualization, where 52 week high and 52 week low would be the values.
So, its for each application with maximum value of ticket counts in a week and minimum value of ticket count in a week.
TABLE
Serv_Req_Number | Create Date | Formatted Date | Application | WeekNum | Count | Year | Month
Serv_Req_Number => Ticket Number
Hi @Anonymous ,
Assuming you have a date table somewhere in the model linked to the ticket table you could use the following combination of measures to generate a min/max week value over the last 52 weeks:
Sum Tickets = COUNT(Tickets[ticketnr])
Max Tickets Week =
MAXX (
SUMMARIZE (
FILTER ( Dates,
Dates[Date] >= TODAY()-365 && Dates[Date] <= TODAY()),
Dates[Year], 'Dates'[Week] ),
if ( ISBLANK([Sum Tickets]), 0 ,[Sum Tickets]) )
Min Tickets Week =
MINX (
SUMMARIZE (
FILTER ( Dates,
Dates[Date] >= TODAY()-365 && Dates[Date] <= TODAY()),
Dates[Year], 'Dates'[Week] ),
if ( ISBLANK([Sum Tickets]), 0 ,[Sum Tickets]) )
hope this helps
@Anonymous Which column are you refering to get the week high and week low number. If I understood it clearly you want to get the max and min number per application per year
Please try a measure something like below
MaxValue = CALCULATE(MAX('Table'[Column]),ALLEXCEPT('Table',Table'[Application],Table'[Created Date].[Year]))
MinValue = CALCULATE(MIN('Table'[Column]),ALLEXCEPT('Table',Table'[Application],Table'[Created Date].[Year]))
If it helps accept solution.
Hi,
I am trying to get Ticket count by application to get 52 week high and 52 week low.
SERV_REQ_NUMBER is Ticket Number.
Thanks,
Mohit
I am trying to get this in matrix visualization, where 52 week high and 52 week low would be the values.
So, its for each application with maximum value of ticket counts in a week and minimum value of ticket count in a week.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |