Measure using DIM date table error

[Anonymous]

Hi,

I am trying to move a Measure from a local date to a DIM Date as I need to compare two different tables.

Date	Deletions	Deletions prior to 31 days ago	Number of deletions in past 31 days
25/06/2020 00:00	16		16
26/06/2020 00:00	17		33
27/06/2020 00:00	14		47
28/06/2020 00:00	28		75
29/06/2020 00:00	25		100
30/06/2020 00:00	54		154
01/07/2020 00:00	20		174
02/07/2020 00:00	17		191
03/07/2020 00:00	8		199
04/07/2020 00:00	10		209
05/07/2020 00:00	6		215
06/07/2020 00:00	3		218
07/07/2020 00:00	11		229
08/07/2020 00:00	17		246
09/07/2020 00:00	10		256
10/07/2020 00:00	10		266
11/07/2020 00:00	6		272
12/07/2020 00:00	10		282
13/07/2020 00:00	15		297
14/07/2020 00:00	6		303
15/07/2020 00:00	11		314
16/07/2020 00:00	11		325
17/07/2020 00:00	12		337
18/07/2020 00:00	9		346
19/07/2020 00:00	9		355
20/07/2020 00:00	12		367
21/07/2020 00:00	9		376
22/07/2020 00:00	5		381
23/07/2020 00:00	11		392
24/07/2020 00:00	12		404
25/07/2020 00:00	9		413
26/07/2020 00:00	5	16	418
27/07/2020 00:00	4	33	406
28/07/2020 00:00	6	47	395
29/07/2020 00:00	9	75	390
30/07/2020 00:00	8	100	370
31/07/2020 00:00	17	154	362
01/08/2020 00:00	22	174	330
02/08/2020 00:00	6	191	316
03/08/2020 00:00	16	199	315
04/08/2020 00:00	9	209	316
05/08/2020 00:00	13	215	319
06/08/2020 00:00	3	218	316
07/08/2020 00:00	7	229	320
08/08/2020 00:00	10	246	319

This is the outcome of the table I would like and it works fine with the local measure but when I switch it to an dim date table as the calendar it goes wrong;

From this

Number of deletions in past 31 days = CALCULATE (     

   SUM('Deletions' [Deletions]),     

   DATESBETWEEN('Deletions'[Date],

   MAX('Deletions' [Date]) - 31,

   Max('Deletions' [Date])))

To this

Number of deletions in past 31 days DIMDATE = CALCULATE (     

   SUM('iOS & Android - Deletions'[Deletions]),     

   DATESBETWEEN(DimDate[Date],

   MAX('Deletions' [Date]) - 31,

   Max('Deletions' [Date])))

I have switched the "Deletions prior to 31 days ago" messure from a local date to use the date dim table and this works just not with this one.

Deletions prior to 31 days ago _ DIMDATE = 
CALCULATE(SUM('Deletions'[Deletions]),
DATESBETWEEN(DimDate[Date],
BLANK(),
MAX('Deletions'[Date]) - 31))

DimDate is created with the below 

DimDate = CALENDAR(date(2014,01,01),date(2020,12,31))

Any idea why it might be going wrong?

Thanks

[v-rzhou-msft]

Hi @Anonymous 

I build two tables like yours to have a test.

Deletions:

DimDate Table:

DimDate = CALENDAR(date(2014,01,01),date(2020,12,31))

Then build a relationship between two tables.

Build measures.

Deletions prior to 31 days ago _ DIMDATE = 
SUMX(FILTER(ALL(Deletions),Deletions[Date]<=MAX(DimDate[Date])),Deletions[Deletions])

Number of deletions in past 31 days_DIMDATE = CALCULATE(SUM(Deletions[Deletions]),DATEADD('DimDate'[Date],-31,DAY))

 Result:

You can download the pbix file from this link: Measure using DIM date table error

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi @v-rzhou-msft 

Thank you for looking in to this for me, I see what maybe my original DAX statment did work and it is having the same issue as yours.

If i look at the time before and after the dataset ends it has the "548" number in the blank date rows as shown below;

When it should look like this;

Any ideas?

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I build a new Deletions prior to 31 days ago _ DIMDATE Measure:

Number of deletions in past 31 days_DimDate = 
VAR _CURRENTDimDATE =
    MAX ( DimDate[Date] )
VAR _MINDATE =
    MINX ( ALL ( Deletions ), Deletions[Date] )
VAR _MAXDATE =
    MAXX ( ALL ( Deletions ), Deletions[Date] )
RETURN
    IF (
        OR ( _CURRENTDimDATE < _MINDATE, _CURRENTDimDATE > _MAXDATE ),
        BLANK (),
        CALCULATE (
            SUM ( 'Deletions'[Deletions] ),
            DATESBETWEEN (
                DimDate[Date],
                MAX ( 'Deletions'[Date] ) - 31,
                MAX ( 'Deletions'[Date] )
            )
        )
    )

Result:

You can download the pbix file from this link: Measure using DIM date table error

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Ashish_Mathur]

Hi,

Ensure that to your visual, you drag Date from the DimDate table.  Also, there should be a relationship from the Date column of the iOS & Android Deletions table to the Date column of the DimDate table.

[lbendlin]

Use a variable to capture your current date value before entering the CALCUALATE() area, and reference the date by the variable.