Cumulative sum when target achieved, get the date

[nisuomi]

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

[Phil_Seamark]

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]

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

[Phil_Seamark]

What is the name of your table?

[nisuomi]

@Phil_Seamark it is named like DataTable.

I replaced MyTable with that but did not work.. 

[Phil_Seamark]

Are you creating it as a calculated measure? 

And did you replace all three occurrences? 

[nisuomi]

@Phil_Seamark could it have something to do with that I have a country as a filter? So it does not how to get the date on country level or something?

Cheers,

Niko

[v-huizhn-msft]

Hi @nisuomi,

You said you have a country as a filter, please check if there is no date for targeted 100, so it returns blank. Based on your description, the issue is werid. Could you share your .pbix for futher analysis?

Best Regards,
Angelia

[pan]

Hi! I'm trying to do the same thing but with measures. I have attached the pbix file. What I am trying to accomplish is to display the month when my target is reached. I did something similar to your suggestion:

Measure = 
VAR __average = [Average of preceding three years]
VAR __cumm = [Cummulative Total]
RETURN
CALCULATE(MINX(DateTable, MIN(DateTable[Date])), FILTER(Products, __average < __cumm),VALUES(DateTable[Date].[Year]))

And this is the result:What I'm trying to do though is to do a yearly report and display the minimum among the results of the measure in the monthy report. In this case, it should display 5/1/2016 in the yearly report but it is displaying 1/1/2016.

[nisuomi]

@Phil_Seamark yes to both of your questions.

I do not know if it has something to do with the some calc or date field..

This is what i have done:

Target Hit Date = CALCULATE( MIN(DataTable[Date]); FILTER(DataTable; DataTable[t_cumu] >= 100 ) )

Cheers,

Niko