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
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 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.