Cumulative Insights

[stiefel.ed]

I am using the DAX formula in the attached image ‘Cumulative2’. My issue is that the cumulative total stretched for every date in my dates table. The other time graphs only show data for entry dates. How do I make it so the scale of the graph stops with the entry dates, rather than scaling to the full dates table length and showing cumulative across that?

[BrianJ]

@stiefel.ed,

I think this video from @sam.mckay should give you what you need to solve this issue:

Cumulative Totals Only Up To Specific Dates - DAX Tutorial For Power BI

• Brian

[stiefel.ed]

Thank you! That is the exact information I am looking for. Oddly, though, when I copy this format exactly, the cumulative total still goes out to Nov 2021 and the last entry date I have for my data is Jan 2021. So its better than going all the way out to 2030, which is where my Dates table ends, but its still not ending at the last entry date.

Any ideas?

[BrianJ]

@stiefel.ed,

From the information provided, I can’t say. But if you can please post your PBIX file, I am sure we can diagnose the problem.

Also, does your date table need to extend to 2030? Alternatively, could you make the end date dynamic (say, to the end of the year subsequent to the current one). Would both decrease size and increase performance.

– Brian

[AntrikshSharma]

@stiefel.ed This will help you.

Hide dates after specific dates in Running Total or any other scenario

If you ever want to create a visual with Running total and hide dates after a certain date then you can first create a calculated column in your date table like this: Date Less Than My Date ? = Dates[Date] < DATE ( 2009, 7, 5 ) [1] After that create a visual with your date hierarchies, I generally use Matrix, and then you can create a piece of DAX like this one [3] And then add a new filter through the filter pane by using the column from the dates table created in the previous step, like…

[stiefel.ed]

That is a really great response, thank you. I don’t want a specific date, however. I want it to be dynamic to the last date entered. The video suggested seems to cover that perfectly and I won’t understand why the logic as written doesn’t work to keep the cumulative date the same as last date entered.

[stiefel.ed]

I like that solution much better! I am not sure how to do that. I just copied the Dates table from a reference folder for EnterpriseDNA training. It asked the query editor for an end date and I didn’t want to have to change it often so I put it way out.

[BrianJ]

@stiefel.ed,

Here’s an incredible post by @Melissa that explains exactly how to do this step-by-step, and also provides a video at the end if you’d rather watch her walk through the process.

Creating a dynamic Start-/EndDate for the Date table

Hi everyone, To make it easier to find here are some Power Query techniques to make the Date table Startdate and/or Enddate dynamic. If you want to give that a try, go to Power Query, select New Source, Blank Query, open the Advanced Editor, paste in this Date table M code and Invoke that function. A new query will appear in the Queries pane with the name ‘Invoked Function’ in the formula bar of that query’s Source step you can distinguish the following elements. Date table function query na…

I now use her approach in almost every report I create.

• Brian

[EnterpriseDNA]

Hi @stiefel.ed, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @stiefel.ed, we’ve noticed that no response has been received from you since the 2nd of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

[EnterpriseDNA]

Hi @stiefel.ed, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!