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

[ClemFandango]

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,

CF

[ClemFandango]

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

CF

[v-xuxinyi-msft · Accepted solution]

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.

[ClemFandango]

Perfection! @v-xuxinyi-msft 

Huge thanks to you!

[DataNinja777]

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
    )

[ClemFandango]

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

[HotChilli]

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.

[some_bih]

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)

[ClemFandango]

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?

[some_bih]

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)

[ClemFandango]

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.