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,

Based on that data you have shared, please check whether the result in this PBI file is correct or not.  You may download it from here.

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks Ashish_Mathur . I feel I am not good in time intelligence functions and will focus on that. 

    as i was not able to use DATESBETWEEN. It perfectly works for scenario I presented but I missed something so sending data with changes below.
     
    Two measures I wanted to find:
    1. Total closed between current snapshot date column and last snapshot date column (that is total closed since last snapshot date  using [state] = "Closed" column)
    2. Total opened between current snapshot and last snapshot ( that is total opened since last snapshot date which will be calculated on the basis of [Created] column between these two dates

    Really sorry for missing description previously. I could not find a way to attach file with data so copied/pasted below

     
     
    SnapshotDateNumberStateCreated
    17/10/2019 00:00110042700Closed07/10/2019 15:18
    17/10/2019 00:00110042741Closed07/10/2019 16:17
    17/10/2019 00:00110043897Closed14/10/2019 12:30
    17/10/2019 00:00110043913Open14/10/2019 13:33
    17/10/2019 00:00110044026Closed15/10/2019 09:26
    17/10/2019 00:00110044077Closed15/10/2019 11:28
    17/10/2019 00:00110044112Closed15/10/2019 12:40
    17/10/2019 00:00110044144Closed15/10/2019 13:42
    17/10/2019 00:00110044169Closed15/10/2019 14:32
    17/10/2019 00:00110044226Closed15/10/2019 15:49
    17/10/2019 00:00110044293Closed16/10/2019 09:50
    17/10/2019 00:00110044326Closed16/10/2019 11:44
    17/10/2019 00:00110044405Open16/10/2019 13:55
    17/10/2019 00:00110044420Open16/10/2019 14:36
    17/10/2019 00:00110044513Open17/10/2019 09:29
    17/10/2019 00:00110044514Open17/10/2019 09:29
    17/10/2019 00:00110044516Closed17/10/2019 09:32
    17/10/2019 00:00110044520Closed17/10/2019 09:36
    17/10/2019 00:00110044559Open17/10/2019 10:52
    23/10/2019 00:00110013192Open17/04/2019 14:39
    23/10/2019 00:00110017705Open20/06/2019 08:44
    23/10/2019 00:00110028225Open04/07/2019 17:10
    23/10/2019 00:00110028300Open05/07/2019 11:48
    23/10/2019 00:00110028341Open05/07/2019 13:23
    23/10/2019 00:00110028343Open05/07/2019 13:34
    23/10/2019 00:00110028796Open09/07/2019 11:18
    23/10/2019 00:00110043873Closed14/10/2019 11:50
    23/10/2019 00:00110043892Closed14/10/2019 12:23
    23/10/2019 00:00110043894Closed14/10/2019 12:24
    23/10/2019 00:00110045260Closed22/10/2019 10:50
    23/10/2019 00:00110045264Closed22/10/2019 10:58
    23/10/2019 00:00110045357Closed22/10/2019 15:47
    23/10/2019 00:00110045431Closed23/10/2019 10:29
    23/10/2019 00:00110045480Closed23/10/2019 11:59
    23/10/2019 00:00110045535Closed23/10/2019 14:08
    23/10/2019 00:00110045584Open23/10/2019 15:35
    01/11/2019 00:00110010380Closed14/03/2019 13:55
    01/11/2019 00:00110020812Open19/07/2019 16:23
    01/11/2019 00:00110045690Closed24/10/2019 10:25
    01/11/2019 00:00110045729Closed24/10/2019 11:38
    01/11/2019 00:00110045779Closed24/10/2019 14:31
    01/11/2019 00:00110045809Closed24/10/2019 17:19
    01/11/2019 00:00110045819Closed24/10/2019 19:34
    01/11/2019 00:00110045838Closed25/10/2019 09:22
    01/11/2019 00:00110046527Open30/10/2019 13:43
    01/11/2019 00:00110046531Open30/10/2019 13:44
    01/11/2019 00:00110046685Open31/10/2019 11:36
    01/11/2019 00:00110046692Open31/10/2019 11:46
    01/11/2019 00:00110046751Open31/10/2019 13:16
    07/11/2019 00:00110010380Closed14/03/2019 13:55
    07/11/2019 00:00110011039Closed20/03/2019 12:05
    07/11/2019 00:00110011743Closed28/03/2019 10:22
    07/11/2019 00:00110035236Open05/11/2019 12:15
    07/11/2019 00:00110035283Open05/11/2019 13:50
    07/11/2019 00:00110035304Open05/11/2019 14:39
    07/11/2019 00:00110014077Open08/04/2019 13:05
    07/11/2019 00:00110014120Open08/04/2019 14:32
    07/11/2019 00:00110014534Open09/04/2019 15:50
    07/11/2019 00:00110043382Closed10/10/2019 09:38
    07/11/2019 00:00110043405Closed10/10/2019 10:33
    07/11/2019 00:00110045675Closed24/10/2019 09:52
    07/11/2019 00:00110045690Closed24/10/2019 10:25
    07/11/2019 00:00110045714Closed24/10/2019 10:53
    07/11/2019 00:00110045729Closed24/10/2019 11:38
    07/11/2019 00:00110045779Closed24/10/2019 14:31
    07/11/2019 00:00110045809Closed24/10/2019 17:19
    07/11/2019 00:00110045819Closed24/10/2019 19:34
    07/11/2019 00:00110045838Closed25/10/2019 09:22
    07/11/2019 00:00110046045Closed28/10/2019 10:26
    07/11/2019 00:00110046075Closed28/10/2019 11:31
    07/11/2019 00:00110046139Closed28/10/2019 14:17
    14/11/2019 00:00110047820Closed07/11/2019 10:32
    14/11/2019 00:00110047841Closed07/11/2019 11:22
    14/11/2019 00:00110047888Closed07/11/2019 13:56
    14/11/2019 00:00110048085Closed08/11/2019 11:32
    14/11/2019 00:00110048199Closed08/11/2019 15:36
    14/11/2019 00:00110048301Closed11/11/2019 10:35
    14/11/2019 00:00110048645Open12/11/2019 12:09
    14/11/2019 00:00110048656Open12/11/2019 12:16
    14/11/2019 00:00110048808Closed12/11/2019 18:30
    14/11/2019 00:00110048839Closed13/11/2019 10:48
    14/11/2019 00:00110048888Closed13/11/2019 11:43
    14/11/2019 00:00110048926Closed13/11/2019 13:35
    14/11/2019 00:00110048931Closed13/11/2019 13:36
    14/11/2019 00:00110049000Closed13/11/2019 16:07
    14/11/2019 00:00110049089Closed14/11/2019 09:54
    14/11/2019 00:00110049117Closed14/11/2019 10:43
    14/11/2019 00:00110049125Closed14/11/2019 10:50
    14/11/2019 00:00110049210Closed14/11/2019 14:37
    14/11/2019 00:00110049232Open14/11/2019 16:10

     

    • 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