Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DATEADD and min function DAX

hi community 

i was hoping someone can help me figure out below 

 

what i want to do is , for each week_start period, i want to bring the minimum of date_update add 13 weeks to it (182 days) and bring the value for that.

 

example for week start 8/8/2022 , i want to bring value of date_update 16/05/2022 which is 11572. and so on ....

 

so everyweek that i have new row of data , it goes back to fine the new min date and add 182 days to it and give me the value 

 

 

i created totalf = SUM(MYDATA[FORECAST_VALUE])

so i thought i can use dateadd function and bring the minimum date_updated and add 182 days to it 

mindate = min(dateadd(MYDATA[DATE_UPDATE] , 182, DAY))

thanks 

DATEADD and min function DAX
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi， Anonymous 

    Please try formula as below:

    Measure =
VAR mindate =
    CALCULATE ( MIN ( 'myData'[DATE_UPDATE] ), ALL ( 'myData' ) )
RETURN
    CALCULATE (
        SUM ( 'myData'[FORECAST_VALUE] ),
        FILTER ( 'myData', 'myData'[DATE_UPDATE] = min_date )
    )

    Best Regards,
    Community Support Team _ Eason

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      thanks for your reply 

      i tried this 

       

      forecastvalue = var min_date = min(myData[DATE_UPDATE])+182
      var forecastv = CALCULATE(SUM(myData[FORECAST_VALUE]) ,myData[DATE_UPDATE] = min_date)
      Return forecastv
       
      and it does not return any values 
       

       

      what i really want to return from all the values for week_start 8/8/2022 is value of 11572 which happend on date_update 16/05/2022

       

       

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        4 years ago

        Hi， Anonymous 

        Please try formula as below:

        Measure =
VAR mindate =
    CALCULATE ( MIN ( 'myData'[DATE_UPDATE] ), ALL ( 'myData' ) )
RETURN
    CALCULATE (
        SUM ( 'myData'[FORECAST_VALUE] ),
        FILTER ( 'myData', 'myData'[DATE_UPDATE] = min_date )
    )

        Best Regards,
        Community Support Team _ Eason