Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
gmasta1129
Helper III
Helper III

Compare Previous Day Data to Today's Data

Hello,

 

I have a simple table which pulls data in each day (except the weekends).  

The table consists of the following columns 

 

1. Customer Number

2. Time Stamp

3. Contract Number

 

I am looking for a formula to compare  today's contract number to previous days contract number and if the contract number is found then pull in "OK". If the contract number is not found then pull in "Missing".

 

For example, in the table below, the contract number "000FXFW230270024" can be found on both 2/6 (today) and 2/3 (previous day) files, therefore, the column should state "OK" but if it was not found on 2/3 file, then it should say "MissIng".

 

Note: This should always be compared to the previous day.  For example,

1. 2/6 data should compare to 2/3 data

2. 2/3 data should be compared to 2/2 data.

3. 2/2 data should be compared to 2/1 data

 
cust #       time stamp      contract number

55555          2/3/2023          0000FXFW230270024

55555          2/6/2023          0000FXFW230270024

44444          2/3/2023          0000FXFW230270023

44444          2/6/2023          0000FXFW230270023

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@gmasta1129  sure

 

Measure = 
VAR prev =
    CALCULATE (
        [__contract],
        OFFSET (
            -1,
            DISTINCT ( ALL ( 'Table' ) ),
            ORDERBY ( 'Table'[timeStamp], ASC ),
            KEEP,
            PARTITIONBY ( 'Table'[cust] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( prev ) = TRUE (), "OK",
        [__contract] = prev, "OK",
        "Missing"
    )

 

smpa01_0-1675718963313.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@gmasta1129  sure

 

Measure = 
VAR prev =
    CALCULATE (
        [__contract],
        OFFSET (
            -1,
            DISTINCT ( ALL ( 'Table' ) ),
            ORDERBY ( 'Table'[timeStamp], ASC ),
            KEEP,
            PARTITIONBY ( 'Table'[cust] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( prev ) = TRUE (), "OK",
        [__contract] = prev, "OK",
        "Missing"
    )

 

smpa01_0-1675718963313.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

 

@smpa01 , thanks for the quick response, the formula has been trying to calculate for the past 10 minutes.  The column is blank.  Not sure what the issue is but please note, that there can be more than one contract number for each customer.  for example, customer number 55555 can have 10 different contract numbers in one day. (2/6/2023) Not usre if the max in the _contract formula is causing the issue. 

 

that there can be more than one contract number for each customer--- the sample data is not representative of this

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.