topic Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months? in DAX Commands and Tips

Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

ClemFandango — Thu, 04 Jul 2024 13:04:42 GMT

Hi there,

I am hoping someone can help.

I have the following function that returns 1 'Count' for each ID where the ‘DateAction’ is within 6 months of ‘Todays_date’.

Calculated column:-

Last6Months = Var endDate = Table1[Todays_Date] Var startDate = EDATE(endDate, -6) RETURN CALCULATE(Table1 [Activity], Table1 [DateAction] >= startDate, Table1 [DateAction] <= endDate)

Measure:-

Activity = COUNTROWS(Table1)

The function works well, but I would like to improve the ‘Edate’ part. Do you have any ideas how I can change the above function to be within the last 180 days, instead of last 6 months?

Table1

Todays_date	DateAction	ID	Count
01/06/24	25/05/24	1	1
01/06/24	16/04/24	1	1
01/06/24	01/12/23	2

Any ideas are greatly appreciated,

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

some_bih — Thu, 04 Jul 2024 14:04:05 GMT

Hi ClemFandango try v2 below

Last6Months v2 =
Var endDate = Table1[Todays_Date]
Var startDate = DATEADD ( endDate, -180, DAY )

RETURN CALCULATE(Table1 [Activity], Table1 [DateAction] >= startDate, Table1 [DateAction] <= endDate)

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

HotChilli — Thu, 04 Jul 2024 14:06:47 GMT

There's something wrong with this part:

RETURN CALCULATE(Table1 [Activity]

no aggregation?

However, for your real question, just subtract 180, I think that'll do it.

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

DataNinja777 — Thu, 04 Jul 2024 14:21:15 GMT

Last_180_days = VAR endDate = Table1[Todays_Date] VAR startDate = MAX ( 'Calendar'[Date] ) - 180 RETURN CALCULATE ( SUM ( Table1[Activity] ), Table1[DateAction] >= startDate, Table1[DateAction] <= endDate )

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

ClemFandango — Fri, 05 Jul 2024 06:46:14 GMT

Hi DataNinja777 HotChilli

Thanks so much for the help.

Apologies as my initial post could of been a bit clearer in hindsight.
In the 'count' column of table1, I am trying to flag if the date was in the last 180 days (it is not technically a running count or sum). The value that I am looking to return in the 'Count' column
is, 1 if it is within 180 days, or blank if older than 180 days.

My original formula appears to work for 6 months, I am just struggling to amend to 180 days.

Table1

Todays_date	DateAction	ID	Count
01/06/24	25/05/24	1	1
01/06/24	16/04/24	1	1
01/06/24	01/12/23	2

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

ClemFandango — Fri, 05 Jul 2024 06:47:50 GMT

Many thanks some_bih , unfortunately when i try to run this, the 'endDate' (in the DATEADD function) is highlighted with a red underline and an error message of "The first argument to 'DATEADD' must specify a column". Any ideas how i can amend the formula?

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

some_bih — Fri, 05 Jul 2024 08:54:11 GMT

Hi ClemFandango try v3

Last6Months v3 =
Var endDate = Table1[Todays_Date]
Var startDate = DATEADD ( Table1[Todays_Date], -180, DAY )

RETURN CALCULATE(Table1 [Activity], Table1 [DateAction] >= startDate, Table1 [DateAction] <= endDate)

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

ClemFandango — Fri, 05 Jul 2024 10:34:45 GMT

Hi again some_bih much appreciated,

This appears to return 1 for everything (no blanks). All rows are incorrectly defined as being within 180 days.

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

ClemFandango — Fri, 05 Jul 2024 11:05:13 GMT

I have spotted another issues with my methodolgy - I am obviously missing something.

I current have this...

Last6Months = Var endDate = Table1[Todays_Date] Var startDate = EDATE(endDate, -6) RETURN CALCULATE(Table1 [Activity], Table1 [DateAction] >= startDate, Table1 [DateAction] <= endDate)

And a measure (I think this is where my issues are arising and my methodology is wrong)

Activity = COUNTROWS(Table1)

But I would like to add another calculated column for the last 3 months (or 90 days).

I thought I would be able to add another calculated column like below, but i get a circular dependency error message 'A circular dependency was detected: Table1[Column], Table[Last_6Months], Table1[Column].'

Last3Months = Var endDate = Table1[Todays_Date] Var startDate = EDATE(endDate, -3) RETURN CALCULATE(Table1 [Activity], Table1 [DateAction] >= startDate, Table1 [DateAction] <= endDate)

Any idea where I am going wrong, and how i can add multipe columns for Last3Months & Last6Months?

I am just trying to return 1 in the 'Count' column where the 'DateAction' is within 6/3 months of 'Todays_Date'

All help eternally appreciated

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

Anonymous — Mon, 08 Jul 2024 08:09:53 GMT

Hi ClemFandango

Thanks for the reply from some_bih , DataNinja777 and HotChilli .

ClemFandango , is the following the result you want? If so, you can try creating the following calculated columns.

Count180days = VAR _last180days = [Todays_date] - 180 RETURN IF([DateAction] >= _last180days && [DateAction] <= [Todays_date], 1, BLANK())

Count90days = VAR _last90days = [Todays_date] - 90 RETURN IF([DateAction] >= _last90days && [DateAction] <= [Todays_date], 1, BLANK())

Best Regards,
Yulia Xu

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

Re: Is there an alternative to Edate that calculates the last 180 days instead of 6 months?

ClemFandango — Mon, 08 Jul 2024 11:54:28 GMT

Perfection! Anonymous

Huge thanks to you!