Carry current month total forward to plot future changes

[MozTom]

Hi All

More rusty on DAX than I thought. I have to import messy Excel data which I'm fine with and can get nearly all the analysis squeezed out of it but I'm stuck on the following...

I'm dealing with long term patients in a facility, some of which have prospective discharge dates. So for each month's data I get I want to provide a dual axis chart showing the number of discharges per (future) month (which I am OK with - by plotting a count of Month Differences against a continuous date axis.

The challenge is, I also want to include a line representing the latest monthly total of patients (with or without discharge dates) which will adjust when the prospective discharges are deducted and shown in the bars. The chart would only need to go into the future as far as the maximum proposed discharge date.

This is a rough mock up of what my data looks like. There are a couple of records that have discharge dates that have expired so eventually should be updated. Each new month may add new patients and have existing patients give new prospective discharge dates - I'm only interested in showing the projections based on the currently available data.

I need a measure to be able to plot the line over future months and drop in the extra axis field well so users can 'project' capacity.

I haven't been able to find examples close enough so any help would be appreciated.

Thanks in advance

[foodd]

Hello @MozTom , and thank you for sharing a question with the Community.   This reply is informtional.   Please remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.  Proud to be a Super User!