Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

cumulative sum issue

Hi, I have the following problem: I have used the following measure to calculate the cumulative sum:

 

This should be the sum of entries in the table: 

 
mit IV = DISTINCTCOUNT(Kunde[amsidnr])
And this should be the cumulative sum:
 
IV =
CALCULATE (
KUNDE[mit IV];
FILTER(
ALL(KUNDE[INSERTDATE].[Date]);
KUNDE[INSERTDATE].[Date] <= MAX(KUNDE[INSERTDATE].[Date])))
 
The formula works fine, but when I use the measure in a graph it shows me all the upcoming months for this year with the value being the same. I only want to see the graph for the monthsuntil now. The slicers don't seem to work. I have no data for the months after January 2019. 
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    This measure should work.

    IV = 
CALCULATE (
KUNDE[mit IV],
FILTER(
ALLSELECTED(KUNDE),
KUNDE[INSERTDATE] <= MAX(KUNDE[INSERTDATE])
))

    Best regards,

    Yuliana Gu

21 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    This measure should work.

    IV = 
CALCULATE (
KUNDE[mit IV],
FILTER(
ALLSELECTED(KUNDE),
KUNDE[INSERTDATE] <= MAX(KUNDE[INSERTDATE])
))

    Best regards,

    Yuliana Gu

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous

    Can you share the pbix? Or at least show the structure of your data model?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      AlB Thanks for the help!

       

      I have a Table called KUNDE with the following columns:

       

      amsidnr : a distinct number assigned to each customer

      INSERTDATE : the date when the new customer was added to the table

      mit IV : number of different amsidnr in the table

      IV : cumulative sum of mit IV over time INSERTDATE

       

      I managed to solve he issue temporarily by setting boundaries to my x-axis, but i don`t want to update those boundaries every day. Without them the visualization would show me data for all the months of 2019.  

       

       

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago
    Anonymous wrote:
    The formula works fine, but when I use the measure in a graph it shows me all the upcoming months for this year with the value being the same. I only want to see the graph for the monthsuntil now. The slicers don't seem to work. I have no data for the months after January 2019. 

     

    Anonymous

    What measure are you referring to? You say they are all calculated columns

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      AlB I am sorry

       

      mit IV and IV are measures

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        I use IV in a graph with INSERTDATE being the x-axis

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous

     

    At the graph do you use the date field from a custom calendar?

     

    If yes then you need to use the date field from the actual table

      • themistoklis's avatar
        themistoklis
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous

         

        If you dont have data after Jan 2019 how do you see the following months then??

         

        Any chance to share your file with us?