Min Temp LY

[ebrownretail]

Hi!

I am trying to pull what the min temp was last year for a specifc date. I have a dates table that is just an extended date table, a weather history table that contains previous year info, and a forecast table to contains the forecast for the next 7 days. 

I am trying to pull what the min temp was last year for the forecasted 7 days. I tied the below measure but it didnt pull anything. Any ideas on how i might be able to do this pleasE?

Min Temp last year =

CALCULATE([Min Temp history], SAMEPERIODLASTYEAR(Dates[Date]), DATESBETWEEN(Dates[Date], CALCULATE(MIN('IBM lookup'[date])), CALCULATE(MAX('IBM lookup'[date]))))

[rajendraongole1]

Hi @ebrownretail- create a measure to get the minimum temperature from the previous year for the forecasted 7 days

create two measures to get the start and end dates of the forecast period

Forecast Start Date = MIN('Forecast Table'[Date])
Forecast End Date = MAX('Forecast Table'[Date])

 use the above two measure in your main measure to get the min temp for the same period last year

Min Temp Last Year =
VAR ForecastStart = [Forecast Start Date]
VAR ForecastEnd = [Forecast End Date]
VAR MinTempLastYear =
CALCULATE(
MIN('Weather History'[MinTemp]),
FILTER(
'Weather History',
'Weather History'[Date] >= DATEADD(ForecastStart, -1, YEAR) &&
'Weather History'[Date] <= DATEADD(ForecastEnd, -1, YEAR)
)
)
RETURN
MinTempLastYear

Hope it helps.

[ebrownretail]

thank you. I just tried this and i get the below error. It would not let me enter the date var in the DATEADD.

The first argument to 'DATEADD' must specify a column.

[muhammad_786_1 · Accepted solution]

You're facing this error because the DATEADD function in DAX expects the first argument to be a column reference, not a scalar value like a measure.

We need to adjust the approach very slightly by using "EDATE".

The main reason for using EDATE is that it directly works with scalar values and allows you to easily manipulate dates by a specific number of months.

Min Temp Last Year =

VAR ForecastStart = [Forecast Start Date]

VAR ForecastEnd = [Forecast End Date]

VAR StartLastYear = EDATE(ForecastStart, -12)

VAR EndLastYear = EDATE(ForecastEnd, -12)

VAR MinTempLastYear =

    CALCULATE(

        MIN('Weather History'[MinTemp]),

        'Weather History'[Date] >= StartLastYear &&

        'Weather History'[Date] <= EndLastYear

    )

RETURN

MinTempLastYear

I hope, it helps you.

Best Regards,
Muhammad Yousaf

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

Follow on LinkedIn