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:SnapshotDateCreatedDateTaskID01/Nov/201920/10/2018120001/Nov/201820/10/2018120101/Nov/201821/10/2018120201/Nov/201822/10/2018120301/Nov/201822/10/2018120401/Nov/201823/10/2018120501/Nov/201824/10/2018120601/Nov/201825/10/2018120710/Nov/201802/11/2018125010/Nov/201802/11/2018125110/Nov/201803/11/2018125210/Nov/201804/11/2018125310/Nov/201804/11/2018125410/Nov/201805/11/2018125510/Nov/201805/11/2018125610/Nov/201806/11/2018125720/Nov/201812/11/2018129020/Nov/201812/11/2018129120/Nov/201813/11/2018129220/Nov/201814/11/2018129320/Nov/201814/11/2018129420/Nov/201815/11/2018129520/Nov/201816/11/2018129630/Nov/201822/11/2018138030/Nov/201823/11/2018138130/Nov/201824/11/2018138230/Nov/201825/11/2018138330/Nov/201826/11/2018138430/Nov/201826/11/2018138530/Nov/201827/11/2018138630/Nov/201827/11/2018138730/Nov/201827/11/2018138830/Nov/201828/11/2018138930/Nov/201829/11/2018139030/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 snapshotdate30/Nov/201835121220/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 DAXOpen 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. RegardsZak

Forum Discussion

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	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

Need Help

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.

8 Replies

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

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:

Total closed between current snapshot date column and last snapshot date column (that is total closed since last snapshot date using [state] = "Closed" column)
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

SnapshotDate	Number	State	Created
17/10/2019 00:00	110042700	Closed	07/10/2019 15:18
17/10/2019 00:00	110042741	Closed	07/10/2019 16:17
17/10/2019 00:00	110043897	Closed	14/10/2019 12:30
17/10/2019 00:00	110043913	Open	14/10/2019 13:33
17/10/2019 00:00	110044026	Closed	15/10/2019 09:26
17/10/2019 00:00	110044077	Closed	15/10/2019 11:28
17/10/2019 00:00	110044112	Closed	15/10/2019 12:40
17/10/2019 00:00	110044144	Closed	15/10/2019 13:42
17/10/2019 00:00	110044169	Closed	15/10/2019 14:32
17/10/2019 00:00	110044226	Closed	15/10/2019 15:49
17/10/2019 00:00	110044293	Closed	16/10/2019 09:50
17/10/2019 00:00	110044326	Closed	16/10/2019 11:44
17/10/2019 00:00	110044405	Open	16/10/2019 13:55
17/10/2019 00:00	110044420	Open	16/10/2019 14:36
17/10/2019 00:00	110044513	Open	17/10/2019 09:29
17/10/2019 00:00	110044514	Open	17/10/2019 09:29
17/10/2019 00:00	110044516	Closed	17/10/2019 09:32
17/10/2019 00:00	110044520	Closed	17/10/2019 09:36
17/10/2019 00:00	110044559	Open	17/10/2019 10:52
23/10/2019 00:00	110013192	Open	17/04/2019 14:39
23/10/2019 00:00	110017705	Open	20/06/2019 08:44
23/10/2019 00:00	110028225	Open	04/07/2019 17:10
23/10/2019 00:00	110028300	Open	05/07/2019 11:48
23/10/2019 00:00	110028341	Open	05/07/2019 13:23
23/10/2019 00:00	110028343	Open	05/07/2019 13:34
23/10/2019 00:00	110028796	Open	09/07/2019 11:18
23/10/2019 00:00	110043873	Closed	14/10/2019 11:50
23/10/2019 00:00	110043892	Closed	14/10/2019 12:23
23/10/2019 00:00	110043894	Closed	14/10/2019 12:24
23/10/2019 00:00	110045260	Closed	22/10/2019 10:50
23/10/2019 00:00	110045264	Closed	22/10/2019 10:58
23/10/2019 00:00	110045357	Closed	22/10/2019 15:47
23/10/2019 00:00	110045431	Closed	23/10/2019 10:29
23/10/2019 00:00	110045480	Closed	23/10/2019 11:59
23/10/2019 00:00	110045535	Closed	23/10/2019 14:08
23/10/2019 00:00	110045584	Open	23/10/2019 15:35
01/11/2019 00:00	110010380	Closed	14/03/2019 13:55
01/11/2019 00:00	110020812	Open	19/07/2019 16:23
01/11/2019 00:00	110045690	Closed	24/10/2019 10:25
01/11/2019 00:00	110045729	Closed	24/10/2019 11:38
01/11/2019 00:00	110045779	Closed	24/10/2019 14:31
01/11/2019 00:00	110045809	Closed	24/10/2019 17:19
01/11/2019 00:00	110045819	Closed	24/10/2019 19:34
01/11/2019 00:00	110045838	Closed	25/10/2019 09:22
01/11/2019 00:00	110046527	Open	30/10/2019 13:43
01/11/2019 00:00	110046531	Open	30/10/2019 13:44
01/11/2019 00:00	110046685	Open	31/10/2019 11:36
01/11/2019 00:00	110046692	Open	31/10/2019 11:46
01/11/2019 00:00	110046751	Open	31/10/2019 13:16
07/11/2019 00:00	110010380	Closed	14/03/2019 13:55
07/11/2019 00:00	110011039	Closed	20/03/2019 12:05
07/11/2019 00:00	110011743	Closed	28/03/2019 10:22
07/11/2019 00:00	110035236	Open	05/11/2019 12:15
07/11/2019 00:00	110035283	Open	05/11/2019 13:50
07/11/2019 00:00	110035304	Open	05/11/2019 14:39
07/11/2019 00:00	110014077	Open	08/04/2019 13:05
07/11/2019 00:00	110014120	Open	08/04/2019 14:32
07/11/2019 00:00	110014534	Open	09/04/2019 15:50
07/11/2019 00:00	110043382	Closed	10/10/2019 09:38
07/11/2019 00:00	110043405	Closed	10/10/2019 10:33
07/11/2019 00:00	110045675	Closed	24/10/2019 09:52
07/11/2019 00:00	110045690	Closed	24/10/2019 10:25
07/11/2019 00:00	110045714	Closed	24/10/2019 10:53
07/11/2019 00:00	110045729	Closed	24/10/2019 11:38
07/11/2019 00:00	110045779	Closed	24/10/2019 14:31
07/11/2019 00:00	110045809	Closed	24/10/2019 17:19
07/11/2019 00:00	110045819	Closed	24/10/2019 19:34
07/11/2019 00:00	110045838	Closed	25/10/2019 09:22
07/11/2019 00:00	110046045	Closed	28/10/2019 10:26
07/11/2019 00:00	110046075	Closed	28/10/2019 11:31
07/11/2019 00:00	110046139	Closed	28/10/2019 14:17
14/11/2019 00:00	110047820	Closed	07/11/2019 10:32
14/11/2019 00:00	110047841	Closed	07/11/2019 11:22
14/11/2019 00:00	110047888	Closed	07/11/2019 13:56
14/11/2019 00:00	110048085	Closed	08/11/2019 11:32
14/11/2019 00:00	110048199	Closed	08/11/2019 15:36
14/11/2019 00:00	110048301	Closed	11/11/2019 10:35
14/11/2019 00:00	110048645	Open	12/11/2019 12:09
14/11/2019 00:00	110048656	Open	12/11/2019 12:16
14/11/2019 00:00	110048808	Closed	12/11/2019 18:30
14/11/2019 00:00	110048839	Closed	13/11/2019 10:48
14/11/2019 00:00	110048888	Closed	13/11/2019 11:43
14/11/2019 00:00	110048926	Closed	13/11/2019 13:35
14/11/2019 00:00	110048931	Closed	13/11/2019 13:36
14/11/2019 00:00	110049000	Closed	13/11/2019 16:07
14/11/2019 00:00	110049089	Closed	14/11/2019 09:54
14/11/2019 00:00	110049117	Closed	14/11/2019 10:43
14/11/2019 00:00	110049125	Closed	14/11/2019 10:50
14/11/2019 00:00	110049210	Closed	14/11/2019 14:37
14/11/2019 00:00	110049232	Open	14/11/2019 16:10

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.

Forum Discussion

Total difference between current and previous date

8 Replies

Related content

Calculate difference from previous month

calculate % difference from previous quarter

Different schema into different destination

Orderbook adjusted for previous periods differences

Calculate Difference Between Previous Row