Forum Discussion

nisuomi's avatar
nisuomi
Icon for Resolver I rankResolver I
9 years ago

Cumulative sum when target achieved, get the date

Hi community,

 

I am facing a bit of a problem with my DAX. Or more specifically the lack of my DAX skills maybe at this point ant the logic is not in my brain.. 

 

I have a situation where we follow cumulative sum and how it progress. We also have a target set according to that cumulative sum. I can create a DAX formula how to get the cumulative sum progress and it nicely shows in the table how it progresses by date. But I can't get the date when the target has achieved. 

 

So I would like to generate a metric where it stores the date when the target has been achieved. The dax I have created always returns a date, that when the sales has started. Any ideas?

 

my dax for the cumulative:

t_cumu = CALCULATE(SUMX(VALUES(Table[Country]); Table[Sales]);
FILTER(
ALL(Table[Date]);
Table[Date] <= MAX(Table[Date])
)
)

 

Here is a screen cap how it works

table

 

So i would love to get the date 11.10.2016 out from this where it has gone over the target... 

If someone could point me to right direction, it would be superb! Thanks :) 

 

 

 

Cheers,

Niko

 

Need Help

9 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Is your Target always 100 or is it variable?

     

    Essentially I would create a measure to apply a filter show rows where t_cumu is higher than the target and then grab the MIN(Date).

     

    I've hardcoded the target but this could be made dynamic

     

     

    Target Hit Date = CALCULATE(
					MIN(MyTable[Date]),
					FILTER(
							MyTable,
							MyTable[t_cumu] > 100
						)
					)

     

    • nisuomi's avatar
      nisuomi
      Icon for Resolver I rankResolver I
      9 years ago

      Hi Phil_Seamark !

       

      Yes the Target is at the moment only 100 so no need to use any variable here.

      I tried the calculation that you provided, but for some reason it returns a (blank) value for me. Any idea what could cause this?

       

      Thank you for your help already :) I try, if I can solve it somehow on my own also .. 

       

       

       

      Cheers,

      Niko