Power BI User Group of Philadelphia

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Compare Snapshot Dates and Count Records on Condition

  • 1.  Compare Snapshot Dates and Count Records on Condition

    Top Contributor
    Posted Jul 14, 2019 03:39 AM
      |   view attached
    Hello,
    I have records that are snapshots each day for a record.  A record's close date can be updated to be either move in/out.  See Example
    Sample Snapshots Data Records

    I need to write a calculation that takes the close date for the first snapshot and compares it against the last snapshot close date. This can't be a calculated column as there will be a snapshot date slicer which the calculation will only include snapshots within the selected range.  I have come up with this measure, which is almost works, but not accurate:
    SUMX(SUMMARIZECOLUMNS(Snap[ID],"Min Close",MIN('Snap'[Close Date]),"Max Close",MAX('Snap'[Close Date]),"Max Snap",MAX('Date'[Date])),IF(AND([Min Close]<[Max Close],[Max Snap]=MAX('Date'[Date])),1,0))​

    The reason (reference id 6) is that if the snapshot dates within the selection started at 2/1 moved to 1/7 and then moved back to 2/1 then Min and Max are wrong because I need to compare the first and last snapshots.  As well, there could be an instance where the first snapshot close date is 1/7 and the last snapshot is 2/1 which is the reverse and I need to count for it.

    I have uploaded a sample file and would appreciate any help.  I have been looking at this for days.


    ------------------------------
    Brian Connelly
    Sr. Associate, RSM US
    Northeast Business Intelligence Practice
    ------------------------------

    Attachment(s)

    pbix
    Sample Snapshots.pbix   49 KB 1 version


  • 2.  RE: Compare Snapshot Dates and Count Records on Condition

    Gold Contributor
    Posted Jul 15, 2019 07:50 AM
    Hi, Brian:

    Could you elaborate a little? It looks to me like you are summarizing by ID, but I wanted to verify. It also looks like the Value field can be ignored for this calculation, or is that necessary to determine whether a record is in or out?

    Your sample data includes 1/1 to 1/8. Could you explain what you want the output values to be for ID 6?

    ------------------------------
    Christopher Schnaars
    Laboratory Testing Inc.
    Hatfield, Pennsylvania
    ------------------------------