Post-deliveries value graph using snapshot data?

[vpsoini]

Hi.

I'm new to PowerBI and just getting started with my first reports. So far so good, but now I could not figure out if the following is possible or not.

I'm trying to track down the evolution of the post-deliveries value. So when customer is ordering goods, which have no balance in the warehouse, it becomes a post-delivery and I'm tracking down the sum over time, BUT...

I have sales order Excel with information which order do have post-deliveries (items, amount and sums) per day. So when I extract the data, it has that moment's snaphot in it. I can show the value, items etc of that moment in the report without any problems. The challenge comes the next day, which has it's own snapshot and values, where some might be the same as before, some new ones might have been added, but the ones which have been fulfilled are removed from the list. Example:

Excel for Day 1

SO 001 - Item1 - value 10$ - delivered 1 - shortage 2 - value of shotrage 20$

SO 001 - Item2 - value 15$ - delivered 5 - shortage 1 - value of shotrage 15$

SO 002 - Item3 - value 20$ - delivered 1 - shortage 1 - value of shotrage 20$

SO 003 - Item2 - value 15$ - delivered 0 - shortage 1 - value of shotrage 15$

SO 004 - Item4 - value 50$ - delivered 0 - shortage 1 - value of shotrage 50$

Total shortage value for day1:  140$

In the end of day 1 the warehouse receives 10pc Item2 and orders missing that item will be filled, but they get couple of new orders with post-deliveries, so the excel for day 2 looks like

Day 2

SO 001 - Item1 - value 10$ - delivered 1 - shortage 2 - value of shotrage 20$

SO 002 - Item3 - value 20$ - delivered 1 - shortage 1 - value of shotrage 20$

SO 004 - Item4 - value 50$ - delivered 0 - shortage 1 - value of shotrage 50$

SO 005 - Item3 - value 20$ - delivered 0 - shortage 1 - value of shotrage 20$

SO 006 - Item5 - value 35$ - delivered 2 - shortage 1 - value of shotrage 35$

Total shortage value for day2:  165$

etc.

I would like be able to capture that total sum change from day to day (here 140$ - 165$ - value of day3 - value of day4) into the graph with this kind of snapshot values to visualize the trend of post-deliveries.  Is that possible?  Is there any kind of "cache" memory for such snapshot values in PowerBI ?

I know I could store the value myself and enter it manually to next day's Excel to get the history, but if there would be some features to help in such case, that would be super...

[v-yalanwu-msft]

Hi, @vpsoini ;

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @vpsoini ;

You could append per day's table as a one table in power query , then create a measure as follows:

1.append two table.

2.create a total measure.

Total = CALCULATE(SUM([shortagevalue]),ALLEXCEPT(Sheet1,'Sheet1'[date]))

The final output is shown below:

If it have day3 ,only append it to table1 ,then it will show three days total and trend graph.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MFelix]

Hi @vpsoini ,

Believe that the best option is to save this snapshots on a daily base to a folder and then use this folder to get the information on the PBI that way you could get the historical data to compare but also get the information on daily information by selecting the date you need.

Below see the two options one is from local folders the other from sharepoint or onedrive

https://docs.microsoft.com/en-us/power-query/connectors/folder

https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links