Forum Discussion

ebrownretail's avatar
ebrownretail
Icon for Resolver I rankResolver I
2 years ago
Solved

Min Temp LY

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]))))
Need Help
  • muhammad_786_1's avatar
    muhammad_786_1
    2 years ago

    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

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      ebrownretail
      Icon for Resolver I rankResolver I
      2 years ago

      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's avatar
        muhammad_786_1
        Icon for Solution Supplier rankSolution Supplier
        2 years ago

        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