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 fo...
Need Help
nisuomi's avatar
nisuomi
Icon for Resolver I rankResolver I
9 years ago

Phil_Seamark it is named like DataTable.

I replaced MyTable with that but did not work.. 

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

Are you creating it as a calculated measure? 

 

And did you replace all three occurrences? 

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

    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

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

    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's avatar
      v-huizhn-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      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's avatar
        pan
        Frequent Visitor
        8 years ago

         

         

        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.