Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Total difference between current and previous date

Hi All,   Could you please help writing DAX code for below query:   I have a dataset with lots of columns but only below columns are more relavent to this query: SnapshotDate CreatedDate Ta...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can use the following measure to meet your requirement:

     

    Total Closed = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Number] ),
    FILTER ( 'Table', [State] = "Closed" )
)

     

    total closed since last snapshot date = 
VAR currentSnapshotDate =
    MAX ( 'Table'[SnapshotDate] )
VAR lastSnapshotDate =
    CALCULATE (
        MAX ( 'Table'[SnapshotDate] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[SnapshotDate] < currentSnapshotDate )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Number] ),
        FILTER (
            'Table',
            'Table'[Created].[Date] <= currentSnapshotDate
                && 'Table'[Created].[Date] > lastSnapshotDate
                && 'Table'[State] = "Closed"
        )
    ) + 0

     

    Total Open = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Number] ),
    FILTER ( 'Table', [State] = "Open" )
)

     

    total open since last snapshot date = 
VAR currentSnapshotDate =
    MAX ( 'Table'[SnapshotDate] )
VAR lastSnapshotDate =
    CALCULATE (
        MAX ( 'Table'[SnapshotDate] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[SnapshotDate] < currentSnapshotDate )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Number] ),
        FILTER (
            'Table',
            'Table'[Created].[Date] <= currentSnapshotDate
                && 'Table'[Created].[Date] > lastSnapshotDate
                && 'Table'[State] = "Open"
        )
    ) + 0

     

    Total calls = 
DISTINCTCOUNT('Table'[Number])

     

    Running Total calls = 
VAR snapshotDate =
    MAX ( 'Table'[SnapshotDate] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Number] ),
        FILTER ( ALLSELECTED ( 'Table' ), [SnapshotDate] <= snapshotDate )
    )

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

Basis the revised data that you have shared in your last post, show me the exact result you are expecting.  Only after my formula's results match yours will i share the solution PBI file with you.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks again Ashish_Mathur 

 

Result will be 

Total closedtotal closed since last snapshot dateTotal OpenTotal Open since last snapshot dateTotal callsRunning Total calls
13136191919
96871736
766111349
160632271
16133161990
61 29 90 

 

and definition of each column will be

 

MeasureDefinition
Total Closedcount for [State] = 'Closed' and [Created] does not matter
total closed since last snapshot datecount for [State] = 'Closed' and [Created] between current [Snapshot date] and last[Snapshot date] - for example on 1st Nov, current snapshot date is 1st Nov and previous is 23rd Oct  so count all closed within these two dates where [created] column is after 23rd Oct and [Created] column is on or before 1st Nov
Total Opencount for State] = 'Open' and [Created] does not matter
Total Open since last snapshot datecount for [State] = 'Open' and [Created] between current [Snapshot date] and last[Snapshot date]
Total callsCount for [Number] for each snapshot
Running Total callsRunning total of new column [Total calls]

 

Thanks for support.

Regards

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    You have not shown the Date column in your expected output.  Also, please review the description in the total "closed since last snapshot date" row.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Ashish_Mathur  for your support through out and your point is valid as I forgot to provide Snapshot date column. 

       

      Thanks a lot again

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can use the following measure to meet your requirement:

     

    Total Closed = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Number] ),
    FILTER ( 'Table', [State] = "Closed" )
)

     

    total closed since last snapshot date = 
VAR currentSnapshotDate =
    MAX ( 'Table'[SnapshotDate] )
VAR lastSnapshotDate =
    CALCULATE (
        MAX ( 'Table'[SnapshotDate] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[SnapshotDate] < currentSnapshotDate )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Number] ),
        FILTER (
            'Table',
            'Table'[Created].[Date] <= currentSnapshotDate
                && 'Table'[Created].[Date] > lastSnapshotDate
                && 'Table'[State] = "Closed"
        )
    ) + 0

     

    Total Open = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Number] ),
    FILTER ( 'Table', [State] = "Open" )
)

     

    total open since last snapshot date = 
VAR currentSnapshotDate =
    MAX ( 'Table'[SnapshotDate] )
VAR lastSnapshotDate =
    CALCULATE (
        MAX ( 'Table'[SnapshotDate] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[SnapshotDate] < currentSnapshotDate )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Number] ),
        FILTER (
            'Table',
            'Table'[Created].[Date] <= currentSnapshotDate
                && 'Table'[Created].[Date] > lastSnapshotDate
                && 'Table'[State] = "Open"
        )
    ) + 0

     

    Total calls = 
DISTINCTCOUNT('Table'[Number])

     

    Running Total calls = 
VAR snapshotDate =
    MAX ( 'Table'[SnapshotDate] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Number] ),
        FILTER ( ALLSELECTED ( 'Table' ), [SnapshotDate] <= snapshotDate )
    )

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks v-lid-msft , this worked perfectly where I tweaked a bit due to what was expected but it was nothing wrong with your DAX code. More imporantly I understood the concept of using variables and jumping between current and previous row. This concept will have long lasting impacts I believe because i am going to use it a lot. 

       

      Thanks a lot for your support.