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:

SnapshotDateCreatedDateTaskID
01/Nov/201920/10/20181200
01/Nov/201820/10/20181201
01/Nov/201821/10/20181202
01/Nov/201822/10/20181203
01/Nov/201822/10/20181204
01/Nov/201823/10/20181205
01/Nov/201824/10/20181206
01/Nov/201825/10/20181207
10/Nov/201802/11/20181250
10/Nov/201802/11/20181251
10/Nov/201803/11/20181252
10/Nov/201804/11/20181253
10/Nov/201804/11/20181254
10/Nov/201805/11/20181255
10/Nov/201805/11/20181256
10/Nov/201806/11/20181257
20/Nov/201812/11/20181290
20/Nov/201812/11/20181291
20/Nov/201813/11/20181292
20/Nov/201814/11/20181293
20/Nov/201814/11/20181294
20/Nov/201815/11/20181295
20/Nov/201816/11/20181296
30/Nov/201822/11/20181380
30/Nov/201823/11/20181381
30/Nov/201824/11/20181382
30/Nov/201825/11/20181383
30/Nov/201826/11/20181384
30/Nov/201826/11/20181385
30/Nov/201827/11/20181386
30/Nov/201827/11/20181387
30/Nov/201827/11/20181388
30/Nov/201828/11/20181389
30/Nov/201829/11/20181390
30/Nov/201829/11/20181391

 

 

This is just made up data manully to create a scenario.

 

So I wanted to show it like below:

SnapshotDateTotal Closed TasksClosed since last snapshotdateOpen since last snapshotdate
30/Nov/2018351212
20/Nov/2018237 
10/Nov/2018168 
01/nov/20188  
    
    
    
    
    

 

Total Closed Tasks = COUNT(TaskID)

Closed Since last snapshotdate = For 30/Nov/2019 there are total 35 closed but on 20/Nov/2018 there were 23 closed so answer should be 12. Please adivse DAX

Open since last snapshotdate = For 30/Nov/2019 - these are all created on or after previous snapshot (20/Nov/2018)  and current snapshotdate 

 

I am novice and tried all DAX experiment but due to movement between current and previous row I struggle. This will be a very good example moving forward to understand and apply moving forward.

 

all your help will be so appreciating and thanks in advance. 

Regards

Zak

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous ,

    You can try to use the following measure formulas if they suitable for your requirements:

    Total closed = 
CALCULATE (
    COUNT ( T1[TaskID] ),
    FILTER ( ALLSELECTED ( T1 ), [SnapshotDate] <= MAX ( T1[SnapshotDate] ) )
)

Previous Closed = 
VAR currDate =
    MAX ( T1[SnapshotDate] )
VAR prevDate =
    CALCULATE (
        MAX ( T1[SnapshotDate] ),
        FILTER ( ALLSELECTED ( T1 ), [SnapshotDate] < currDate )
    )
RETURN
    IF (
        prevDate <> BLANK (),
        CALCULATE (
            COUNT ( T1[TaskID] ),
            FILTER (
                ALLSELECTED ( T1 ),
                [SnapshotDate] <= currDate
                    && [SnapshotDate] > prevDate
            )
        )
    )

Last Closed =
VAR currDate =
    MAX ( T1[SnapshotDate] )
VAR _lastDate =
    CALCULATE ( MAX ( T1[SnapshotDate] ), ALLSELECTED ( T1 ) )
RETURN
    IF (
        currDate = _lastDate,
        CALCULATE ( COUNT ( T1[TaskID] ), VALUES ( T1[SnapshotDate] ) )
    )

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

    You can try to use the following measure formulas if they suitable for your requirements:

    Total closed = 
CALCULATE (
    COUNT ( T1[TaskID] ),
    FILTER ( ALLSELECTED ( T1 ), [SnapshotDate] <= MAX ( T1[SnapshotDate] ) )
)

Previous Closed = 
VAR currDate =
    MAX ( T1[SnapshotDate] )
VAR prevDate =
    CALCULATE (
        MAX ( T1[SnapshotDate] ),
        FILTER ( ALLSELECTED ( T1 ), [SnapshotDate] < currDate )
    )
RETURN
    IF (
        prevDate <> BLANK (),
        CALCULATE (
            COUNT ( T1[TaskID] ),
            FILTER (
                ALLSELECTED ( T1 ),
                [SnapshotDate] <= currDate
                    && [SnapshotDate] > prevDate
            )
        )
    )

Last Closed =
VAR currDate =
    MAX ( T1[SnapshotDate] )
VAR _lastDate =
    CALCULATE ( MAX ( T1[SnapshotDate] ), ALLSELECTED ( T1 ) )
RETURN
    IF (
        currDate = _lastDate,
        CALCULATE ( COUNT ( T1[TaskID] ), VALUES ( T1[SnapshotDate] ) )
    )

    Regards,

    Xiaoxin Sheng