Forum Discussion

omarelmb123's avatar
omarelmb123
Icon for Helper I rankHelper I
2 years ago

'DATEADD' only works with contiguous date working with multiple year selection

Hello, im using a simple formula and calculaing leavers for each month and comparing it by year.

The rule is the leavers on the last day of the month should be counted as leavers for the next month 

Leavers = Calculate(Count(ID) , DateAdd(Date, -1 , Days))

My date tablle relation is unidirectional 

the formula works well the problem is when i have multiple year selection and filtering by month it gives this error : 

MDX Error : mdxscript(model) dateadd contagious date.

anyway to solve the problem, any other function to use ?

Need Help

3 Replies

  • MNedix's avatar
    MNedix
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi,

    Create a dedicated Date table with unique (and contiguous dates), create a relationship between the new date table and the date column on the Leavers table and then use the new Date in the formula.

     

    • omarelmb123's avatar
      omarelmb123
      Icon for Helper I rankHelper I
      2 years ago

      I have already a table with unique values with 1 to Many relation and unidirectional . the thing is the formula is working but when i add a third dimension, for example location then i got the error

  • MNedix's avatar
    MNedix
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hi omarelmb123 

    Sorry for the late response. I was able to replicate your error with one of my data sets; the data set is based on sale ID but you can use anything (as in your case - Leavers).

    Given that:

    Table = your table with Leavers (Sale IDs in my case)

    Date = your date table (with relationship with CloseDate in my Table - your Leaver date in yours)

    _Total Count = the count of total distinct entries (e.g. sale ID or Leavers)

    _EoM Count = the count of entries on the last day of each month (for example Sale Completed or Leaver left)

    _pEoM Count = the count of entries on the last day of the previous month

     

    Then:

    _Current Month = [_Total Count]-[_EoM Count]+[_pEoM Count]

     

    Having said that:

    _Total Count = CALCULATE(DISTINCTCOUNT(Table[Id])

_EoM Count = 
var _eom=ENDOFMONTH('Date'[Date])
RETURN
CALCULATE(COUNT(Table[Id]),FILTER(ALLSELECTED(Table),Table[CloseDate]=_eom))

_pEoM Count = CALCULATE([_EoM Count],DATEADD('Date'[Date],-1,MONTH))

     You can bundle all of them in one formula by making them variables but I wanted to show you the logical thread.

     

    I then get the below screenshots:

    1. Showing all data points with no filters

    2. Filtered for something (in my case I have New/Existing/Unknown business)

     

    As always, if this answered your questions then please mark it as the solution so others can see it.