How to Evaluate a table of Snapshot Data

[Ray_Brosius]

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

• Num Tickets in Status at start and Num Tickets in Status at end,
• Num Tickets in Category at start and Num Tickets in Category at end
• Cost of Tickets by Category at start, at end and "delta"(did cost go up or down)

• Num tickets in Each Status that moved to another Status ( i.e. how many tickets that were in NEW at start went to "in-Progresss" and how many of those new tickets ended in "Complete")  

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

[v-rongtiep-msft]

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.

[Ray_Brosius]

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.. 

• Num Tickets in a specific Status at start and Num Tickets in Status at end,
• Num Tickets in Category at start and Num Tickets in Category at end
• Cost of Tickets by Category at start, at end and "delta"(did cost go up or down)

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

• Num tickets in Each Status that moved to another Status ( i.e. how many tickets that were in NEW at start went to "in-Progresss" and how many of those new tickets ended in "Complete")