Forum Discussion
Measure using DIM date table error
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
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.
5 Replies
Use a variable to capture your current date value before entering the CALCUALATE() area, and reference the date by the variable.
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.
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.
Hi Anonymous
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?
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.
