Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
omarelmb123
Helper I
Helper I

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

3 REPLIES 3
MNedix
Solution Supplier
Solution Supplier

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.

 

DateAdd_1.jpgDateAdd_2.jpg

MNedix
Solution Supplier
Solution Supplier

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.

 

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.