Total difference between current and previous date

[Anonymous]

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	TaskID
01/Nov/2019	20/10/2018	1200
01/Nov/2018	20/10/2018	1201
01/Nov/2018	21/10/2018	1202
01/Nov/2018	22/10/2018	1203
01/Nov/2018	22/10/2018	1204
01/Nov/2018	23/10/2018	1205
01/Nov/2018	24/10/2018	1206
01/Nov/2018	25/10/2018	1207
10/Nov/2018	02/11/2018	1250
10/Nov/2018	02/11/2018	1251
10/Nov/2018	03/11/2018	1252
10/Nov/2018	04/11/2018	1253
10/Nov/2018	04/11/2018	1254
10/Nov/2018	05/11/2018	1255
10/Nov/2018	05/11/2018	1256
10/Nov/2018	06/11/2018	1257
20/Nov/2018	12/11/2018	1290
20/Nov/2018	12/11/2018	1291
20/Nov/2018	13/11/2018	1292
20/Nov/2018	14/11/2018	1293
20/Nov/2018	14/11/2018	1294
20/Nov/2018	15/11/2018	1295
20/Nov/2018	16/11/2018	1296
30/Nov/2018	22/11/2018	1380
30/Nov/2018	23/11/2018	1381
30/Nov/2018	24/11/2018	1382
30/Nov/2018	25/11/2018	1383
30/Nov/2018	26/11/2018	1384
30/Nov/2018	26/11/2018	1385
30/Nov/2018	27/11/2018	1386
30/Nov/2018	27/11/2018	1387
30/Nov/2018	27/11/2018	1388
30/Nov/2018	28/11/2018	1389
30/Nov/2018	29/11/2018	1390
30/Nov/2018	29/11/2018	1391

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

So I wanted to show it like below:

SnapshotDate	Total Closed Tasks	Closed since last snapshotdate	Open since last snapshotdate
30/Nov/2018	35	12	12
20/Nov/2018	23	7
10/Nov/2018	16	8
01/nov/2018	8

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

[Anonymous · Accepted solution]

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