cumulative sum issue

[Anonymous]

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. 

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

This measure should work.

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

Best regards,

Yuliana Gu

[Anonymous]

@v-yulgu-msft Thanks!

[themistoklis]

@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

[Anonymous]

@themistoklis

I use the date field from my table. 

[themistoklis]

@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?

[themistoklis]

@Anonymous

Following my previous message you can add another parameter on your filter section:

IV =
CALCULATE (
KUNDE[mit IV];
FILTER(
ALL(KUNDE[INSERTDATE].[Date]);
KUNDE[INSERTDATE].[Date] <= MAX(KUNDE[INSERTDATE].[Date]) &&
MONTH(KUNDE[INSERTDATE]) <= MONTH(TODAY()) 
)
)

[Anonymous]

@themistoklis

thank you for the reply but your formula only returns the values for all januarys. I created another table with sample data that i would like to share here but i don´t know how to upload it. Sorry I am new can you please help with that too.

[themistoklis]

@Anonymous

either use dropbox or onedrive and share it  ... or you can even upload the file on a website (e.g. wetransfer.com) and send the link to us

[Anonymous]

https://www.dropbox.com/s/71l5n0t0d8ar6qc/Test.pbix?dl=0

[Anonymous]

@themistoklis there it is

[themistoklis]

@Anonymous

Remove the .Date from your DAX formula. It should be like this:

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

[Anonymous]

@themistoklis

it doesn´t work. If I do that it doesn´t show a cumulative sum anymore

[themistoklis]

@Anonymous

I sent another message where i made one more change to the DAX formula.

This one does show the cumulative sum.

Posting it again:

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

[Anonymous]

@themistoklis

thanks it worked for the sample dataset but I am still having trouble in the original one, because some of the INSERTDATE fields are blank. Thanks a lot once again though

[Anonymous]

@themistoklis

it worked like that:

IV = 
CALCULATE (
  KUNDE[mit IV];
    FILTER(
       ALL(KUNDE);
       KUNDE[INSERTDATE].[Date] <= MAX(KUNDE[INSERTDATE].[Date])))

[themistoklis]

@Anonymous

I have also made one more correction as the above formula doesnt give the cumulative sum:

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

[AlB]

---

@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]

@AlB I am sorry

mit IV and IV are measures

[Anonymous]

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

[AlB]

Hi @Anonymous

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