Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 3...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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?

 

 

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.