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.
Hello all,
I have a question on how to best develop methods to evaluate Data that is daily snapshots.
Below I have shared an example dataset and the outline of what I want to do..
My sample is a small set of "Tickets" in a table that has a snapshot of ALL tickets for each day.
For my "report" level I will provide the user with a method to evaluate any Snapshot(day) against another Snapshot(day)... so the evaluation of Tickets will be "dynamic" and selected by the report user. They will pick the "starting" snapshot date and the "ending" snapshot date. The Report will then evaluate each ticket and the "changes" on a ticket by ticket basis and also in "aggregate"..
Things like
Key is to be able to validate that the ticket at the end snapshot was in the Start Snapshot.. and determine it's changes. Identify the tickets that were NOT in the starting snapshot and count them as "ADDED" since start.
Want to be able to look across each snapshot and be able to calculate the differences in Cost, changes in Status, Changes in Category.
Ticket ID | Date Created | Due Date | ResolutionDate | Status | Cost | Category | Snapshot |
123 | 1/1/2022 | 3/1/222 | New | $5 | Hardware | 1/5/2022 | |
456 | 1/1/2022 | 3/15/2022 | In-Progress | $9 | Hardware | 1/5/2022 | |
789 | 1/3/2022 | 2/18/2022 | In-Progress | $10 | Software | 1/5/2022 | |
123 | 1/1/2022 | 3/5/2022 | In-Progress | $6 | Misc | 1/6/2022 | |
456 | 1/1/2022 | 3/15/2022 | 1/6/2022 | Complete | $9 | Hardware | 1/6/2022 |
789 | 1/3/2022 | 2/18/2022 | In-Progress | $7 | Software | 1/6/2022 | |
123 | 1/1/2022 | 3/5/2022 | In-Progress | $6 | Misc | 1/6/2022 | |
456 | 1/1/2022 | 3/15/2022 | 1/6/2022 | Complete | $9 | Hardware | 1/6/2022 |
789 | 1/3/2022 | 2/18/2022 | In-Progress | $20 | Software | 1/6/2022 | |
1234 | 1/7/2022 | 4/1/2022 | New | $21 | Software | 1/7/2022 | |
123 | 1/1/2022 | 3/5/2022 | In-Progress | $6 | Misc | 1/7/2022 | |
456 | 1/1/2022 | 3/15/2022 | 1/6/2022 | Complete | $9 | Hardware | 1/7/2022 |
789 | 1/3/2022 | 2/18/2022 | In-Progress | $15 | Software | 1/7/2022 |
Hi @Ray_Brosius ,
Could tell me what is your desired output? Only calculate the differences in Cost, changes in Status, Changes in Category.
If so, please have a try.
Create measures.
Status-change =
VAR meaasure_ =
CALCULATE (
MAX ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
'Table'[Ticket ID] = SELECTEDVALUE ( 'Table'[Ticket ID] )
&& 'Table'[Date Created] = SELECTEDVALUE ( 'Table'[Date Created] )
)
)
VAR measure2_ =
CALCULATE (
SELECTEDVALUE ( 'Table'[Status] ),
FILTER (
ALL ( 'Table' ),
'Table'[Snapshot] >= SELECTEDVALUE ( 'Table'[Snapshot] )
&& 'Table'[Ticket ID] = SELECTEDVALUE ( 'Table'[Ticket ID] )
)
)
RETURN
IF (
ISBLANK ( measure2_ ),
meaasure_,
IF (
measure2_ = "Complete",
"Complete",
IF ( meaasure_ = measure2_, meaasure_, meaasure_ & "-" & measure2_ )
)
)
categoey-change =
VAR aststus =
CALCULATE (
MAX ( 'Table'[Category] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date Created] = SELECTEDVALUE ( 'Table'[Date Created] )
&& 'Table'[Ticket ID] = SELECTEDVALUE ( 'Table'[Ticket ID] )
)
)
VAR another =
CALCULATE (
MAX ( 'Table'[Category] ),
FILTER (
'Table',
'Table'[Date Created] >= SELECTEDVALUE ( 'Table'[Date Created] )
)
)
RETURN
IF ( another = aststus, another, aststus & "-" & another )
price difference =
VAR _re =
CALCULATE (
MAX ( 'Table'[Cost] ),
FILTER ( 'Table', 'Table'[Snapshot] = SELECTEDVALUE ( 'Table'[Snapshot] ) )
)
RETURN
_re - MAX ( 'Table'[Cost] )
Then you can use slicer to filter the date.
If I have misunderstood your meaning, please provide your desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Polly,
Thanks for your reply.. I think I was not clear in the Analysis need... Each snapshot shows the VALUE of each column for the tickets on that given day.. We want to be able to evaluate the below between any two snapshot dates that the user can select at Run -Time..
Combine in one chart the Ticket Movement to different Status and show the Cost as well..
So Summ of cost of tickets in each Status and the Number of those Tickets at the Start
THEN
Sum of the cost of tickets in each status and the Number of those Tickets at the end.
Count the number of "NEW" tickets added by Status.. That being the Tickets that were in the Ending snaphot but not in the First Snapshot. ( *** again the keeping in mind the dynamic ability of the user to select the start and end snapshot dates to evaluate ***)
Analyse the Tickets that had a "cost" change.. Number of tickets that increased cost and Number of tickets that had a decrease in cost.. And the Amount of change...
Flow analysis
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 |