Filter on Cumulative YOY graph not working

[Mattbidev101]

I have this report that you see here with an issue on my cumulative graph.  When you hover over the bar in the cumulative revenue graph, you'll see that the cumulative revenue tooltip doesn't match the total for 2021 revenue in the summary table.   

Here is all I know so far:

When i review this dax I used to create that cumulative measure, removing the section in red related to the "product mix of orders" filter on the report seems to correct the calculation (as long as i don't filter on that item).

CumulativeRevenue = CALCULATE(SUM(TreeNursuryLineItems[Amount]),FILTER(ALL(TreeNursuryLineItems),TreeNursuryLineItems[Order Date] <= MAX(TreeNursuryLineItems[Order Date])),VALUES(TreeNursuryLineItems[FiscalYear]),VALUES(TreeNursuryLineItems[Order Status]),VALUES(TreeNursuryLineItems[Project_Type]),VALUES(TreeNursuryLineItems[ProductGroup]))

Additionally, when i convert the bar graph to a table and highlight the 2021 portion of the summary table, the numbers are correct and adjust with any of the filters (slight difference from rounding change).

What am i missing so that the numbers in the bar graph are not correct?

Here is a link to the pbxi file to view.

https://arbordayfarm-my.sharepoint.com/:u:/g/personal/mbaldwin_arborday_org/EXbseJigroJEjZR3jeNfBkAB... 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Mattbidev101]

Thanks for replying.  I think I was able to share the pbx file...

[Ashish_Mathur]

Hi,

You may download the PBI file from here.

Hope this helps.

[Mattbidev101]

Thanks for taking a look at this.  One thing i noticed was that on the file you shared, the current month's data is replicated across future months for the current Fiscal year (20-21).  I couldn't figure out how to cut that off  October on your code like you see in my original.  Also, I've tried using DATESYTD function before to build this graph and didn't get the same result.  What was the key here?  was it just having that Dates table that you built?

[Ashish_Mathur]

Hi,

You are welcome.  You may modify my cumulative revenue measure with an IF statement.  If the revenue is 0, then blank else my cumultive revenue measure.  If you are unable to do it, post back.

[Mattbidev101]

I tried replicating the changes you suggested in my original file but the data is not cumulative.  what am i missing in these steps?  Here is the file again with my attempt at your suggestions.

https://arbordayfarm-my.sharepoint.com/:u:/r/personal/mbaldwin_arborday_org/Documents/communityhelp.... 

[Ashish_Mathur]

Hi,

That takes me to a sign-in page.

[Mattbidev101]

oops.  Try this one.

https://arbordayfarm-my.sharepoint.com/:u:/g/personal/mbaldwin_arborday_org/EXbseJigroJEjZR3jeNfBkAB...

[Ashish_Mathur]

Hi,

I have edited the formula in my solution file i shared with you earlier.  Download the revised file from here.

Hope this helps.

[Mattbidev101]

I appreciate you sticking with me!  However it's still not working rolling up cumulative. this dax that you shared:

Cumulative revenue1 = IF(ISBLANK([Revenue]),BLANK(),CALCULATE([Revenue],DATESYTD(Dates[Date],"30/6")))

and the dax i used:  

Cumulative revenue1 = IF([Revenue] = 0,"",CALCULATE([Revenue],DATESYTD(Dates[Date],"30/6")))

should function the exact same way.  I'm missing something else that you are doing in your file to make this work.  Can you see what I'm missing in the file I shared?  When you shared your solution the first time, i tried to find everything that you did but I must have missed something important.

[Ashish_Mathur]

Hi,

Why can you not just apply my formula?  I have spent quite some time in framing that solution and would not like to devote any more time in identifying the problem in your file.  Hope you understand.  Thank you.

[Mattbidev101]

I did apply that formula to make sure.  It doesn't work.  Is there something else that you did besides just that function?

[Ashish_Mathur]

No, i did not.

[Mattbidev101]

Ok thanks for trying.  You've given me some stuff to look at least.  If i manage to solve it I'll publish something for anyone interested.

[Ashish_Mathur]

Thank you for your understanding.  I would have looked at your file had i already not spent enough time in framing the original solution.  Furthermore, in the file that i shared with you, it is working absolutely fine.  Compare the Calendar table and the relationships.

[Mattbidev101]

I will thanks