Forum Discussion

janainacarmo's avatar
janainacarmo
Regular Visitor
6 years ago

Cumulate quantity

Hello Guys!

 

Please, see if you guys can help me with this question.
I have to calculate the accumulate year to year of quantity, as example below:

 

A car has to have his oil changed every year, so, if I have 10 cars having  their oil changed in 2017 and 20 cars having their oil changed in 2018, the index has to calculate:

2017: 10

2018: 20 + 10 (from 2017, because the same 10 cars in 2017, has to have the oil changed again in 2018)

Total: 40 cars   

 

I created the following index that is working partially, it means that is calculating right, but if I change the period to see the data just in 2018, the index continue calculating from 2017, so it's is showing 40 quantity rather than 20 .

 

measure = CALCULATE (
SUMX(TIV;[Valor]);
FILTER(
ALLEXCEPT(TIV;TIV[Linha];TIV[Segmento]);
'TIV'[year] <= MAX ( 'TIV'[year])); ALLEXCEPT(TIV;TIV[Linha];TIV[Segmento]);TIV[Brand]="Other"
)
 
Waiting for your support guys, thank you!
Janaina
Need Help

4 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi janainacarmo ,

     

    This cumulative count works in dashboard and works with a time slicer and a program slicer.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

    Cumulative Count of Customers = 

VAR MaxDate = MAX ( aver[Date] )-- Saves the last visible date
var Counter =
    CALCULATE (
        [Count of customers],            -- Computes customers
        aver[Date]<= MaxDate,   -- Where date is before the last visible date
        ALLEXCEPT( aver,aver[Program] )              -- Removes any other filters from Date allexcept aver([Program]

   )
return Counter
    • janainacarmo's avatar
      janainacarmo
      Regular Visitor
      6 years ago

      Dear Nathaniel_C ,

       

      Thank you for your reply, but it still not working properly...the cumulative is working fine, but when I change the períod for 2017-2019, it still being summing the value from 2016. The total from the first year, in the period that I chose in the time slicer, should not be cumulative. I attached two prints (períod 2016-2019 and 2017-2019) and highlighted them in yellow as you can see.

       

      https://drive.google.com/drive/folders/1TBOsDKqiGUfnsGZugNfcZfF4-OiDCtYm?usp=sharing

       

      Thank you for your support until now.

      Best Regards, Janaina

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    janainacarmo ,

     

    You may modify your measure using dax below:

    measure =
CALCULATE (
    SUMX ( TIV; [Valor] );
    FILTER (
        ALLEXCEPT ( TIV; TIV[Linha]; TIV[Segmento] );
        TIV[Brand] = "Other"
            && 'TIV'[year] IN VALUES ( 'TIV'[year] )
    )
)

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • janainacarmo's avatar
      janainacarmo
      Regular Visitor
      6 years ago

      Dear v-yuta-msft ,

       

      Thank you for your reply, but this index is not working as cummulative...it is just counting the value year per year.

       

      Thank you for your supporting until now.

       

      Best Regards, Janaina