Trying to chart order status changes throughout a working day period using a variety of measures

[Rob_Davidson]

Hi there, I am a relative novice when it comes to Dax so need some help if possible. I am faced with the prospect of taking the various stages of customer orders and graphing the changes over the current work day, which for this purpose is 6am until 5pm. The source data looks like:

So far, using various fill up and fill down measures I have managed to get the status the order started the day with and for each minute from 6am to 5pm the status would show as the latest status at that point, changing to a new status when the status is actually updated - its worth pointing out that I have also added in a measure to group the statuses and swap them out for 4 distinct options. I am then trying to plot this on an area chart so that when you hover over a particular time point it would say a count of all of the orders at the various stages at that point. I hope I have explained it clearly and hope someone can help me out. I have provided screen shots of all of the measures I have used to get me to where I am at the moment.

The Time table I have used:

The next step in all of this is creating a measure that will count the number of distinct orders at each stage so it could be plotted in a chart such that when someone hovers over a particular time point they could see the number of orders at the 4 different stages at that point. Im struggling with the next measure(s) though in trying to get accurate counts by status. Any help much appreciated. Thanks!

[Anonymous · Accepted solution]

HI @Rob_Davidson,

Measure expressions are calculated based on specific row and filter contents.

Row Context and Filter Context in DAX - SQLBI
If you want to invoke specific result, I’d like to suggest you create a variable table with summarize function to reproduce the calculations field and groups of that visualization.
Then you can add a custom field to invoke previous measure and use iterator aggregate function to summary these results.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng