Forum Discussion

pan's avatar
pan
Frequent Visitor
8 years ago

Running total then return date when target is reached?

Hi!

 

I have data arranged as follows:

 

Year, Product , Gross Sales

 

I then created measures for Rolling Averages (for preceding three years. This serves as the Target), Running totals (would reset annually), and Target Status(whether the running totals exceeds the rolling average).

 

Now, I want to return the Month (using a date table) by finding the minimum date where running total is greater than target but since I'm using a measure as reference and not an absolute value, an error is returned that a Calculate function is used in a filter and that it is not allowed. How would I go around this?

Need Help

2 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    pan

     

    You should apply all dates as row context in FILTER() function to compare two measures. 

     

    AchieveDay = CALCULATE(MIN(Table4[Date]),FILTER(ALL(Table4[Date]),[ytd]>[AVG]))

     

    Regards,

     

    • pan's avatar
      pan
      Frequent Visitor
      8 years ago

      Hi! Thanks for your response. But I also want to filter by product. Using your formula, it only returns the same date regardless of the product. I'm sorry I'm really new to Power BI