cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

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

1 ACCEPTED SOLUTION
Community Support

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.

10 REPLIES 10
Helper III

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

Community Support

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.

Helper III

Perfection! @v-xuxinyi-msft

Huge thanks to you!

Super User
``````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
)``````
Helper III

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
Super User

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.

Super User

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper III

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?

Super User

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper III

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors