Calculated column DAX

[JumoGra]

Hi There I have two calculated columns that show a 1 value for all dates that fall with a specified time period but I will like to change my calculations slightly to get a slightly different result. Many thanks.

This Year =

Var _StartDate = Date(YEAR(Today()),1,1)

Var _EndDate = TODAY()-1

return

IF('CDCalendar'[CallDate]>=_StartDate && 'CDCalendar'[CallDate]<=_EndDate,1,0)

I will like the DAX formula to include all dates a year ago as at yesterday, yesterday being (26/04/2024) so I want it to return values for (27/04/23-26/04/24)

The same applies for my this:

This Month =

Var _StartDate = EOMONTH(TODAY(),-1)+1

Var _EndDate = TODAY()-1

return

IF('CDCalendar'[CallDate]>=_StartDate && 'CDCalendar'[CallDate]<=_EndDate,1,0)

I will like the DAX formula to include all dates a month ago as at yesterday, yesterday being (26/04/2024) so I want it to return values for (27/03/24-26/04/24)

[ryan_mayu · Accepted solution]

@JumoGra 

 (27/04/23-26/04/24)

pls try 

=

Var _StartDate = Edate(TODAY(),-12)+1

Var _EndDate = TODAY()-1

return

IF('CDCalendar'[CallDate]>=_StartDate && 'CDCalendar'[CallDate]<=_EndDate,1,0)

(27/03/24-26/04/24)

pls try 

=

Var _StartDate = EDATE(TODAY(),-1)+1

Var _EndDate = TODAY()-1

return

IF('CDCalendar'[CallDate]>=_StartDate && 'CDCalendar'[CallDate]<=_EndDate,1,0)

[JumoGra · Accepted solution]

@AnalyticsWizard Thanks for your reply, this worked with some minor tweaks only because I changed my mind on how many months I wanted to see, thanks for explaining it!

[ryan_mayu · Accepted solution]

@JumoGra 

 (27/04/23-26/04/24)

pls try 

=

Var _StartDate = Edate(TODAY(),-12)+1

Var _EndDate = TODAY()-1

return

IF('CDCalendar'[CallDate]>=_StartDate && 'CDCalendar'[CallDate]<=_EndDate,1,0)

(27/03/24-26/04/24)

pls try 

=

Var _StartDate = EDATE(TODAY(),-1)+1

Var _EndDate = TODAY()-1

return

IF('CDCalendar'[CallDate]>=_StartDate && 'CDCalendar'[CallDate]<=_EndDate,1,0)

[JumoGra · Accepted solution]

This works as well, thank you!

[ryan_mayu]

you are welcome

[AnalyticsWizard]

@JumoGra 

To modify your DAX calculations for the "This Year" and "This Month" columns to reflect the periods ending yesterday, you can adjust the variables for start and end dates accordingly. Below are the updated DAX formulas based on your requirements:

For "This Year"

This adjusted formula calculates the period from 27th April of the previous year to 26th April of the current year, as of yesterday (26th April 2024):

This Year =
VAR _EndDate = TODAY() - 1
VAR _StartDate = DATE(YEAR(_EndDate) - 1, MONTH(_EndDate) + 1, DAY(_EndDate))
RETURN
IF('CDCalendar'[CallDate] >= _StartDate && 'CDCalendar'[CallDate] <= _EndDate, 1, 0)

Explanation:
- `_EndDate` is set to yesterday's date.
- `_StartDate` is set to the same day and month as `_EndDate` but from the previous year. This setup gives you the time period starting from 27th April of the last year to 26th April of the current year.

For "This Month"

This formula will calculate for the period from the 27th of the previous month to the 26th of the current month, as of yesterday (26th April 2024):

This Month =
VAR _EndDate = TODAY() - 1
VAR _StartDate = DATE(YEAR(_EndDate), MONTH(_EndDate), DAY(_EndDate) + 1) - 1
RETURN
IF('CDCalendar'[CallDate] >= _StartDate && 'CDCalendar'[CallDate] <= _EndDate, 1, 0)

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[JumoGra · Accepted solution]

@AnalyticsWizard Thanks for your reply, this worked with some minor tweaks only because I changed my mind on how many months I wanted to see, thanks for explaining it!