Capturing last month's data

Power Query Data Setup
1

I have a Power Bi report where the data comes from an ODatafeed that is refreshed every 2 hours.
I can sum the amount of unpaid invoices at a point in time. For example when I look at the total on 1st April, unpaid invoices = £100
Invoices will be paid, and new ones will be generated. When I look at the total on 1st May, unpaid invoices = £75.
How do I calculate “total amount the unpaid invoices has CHANGED since last month” in other words £100 - £75 = £25?

I cant work out how to capture the £100, since that data has now changed.

I need to capture the data at a point in time (without manually exporting the data to Excel every month).

How do I do it?

2

Hi @RoseyAlexa,

Welcome back to the forum.

Typically for this sort of requirement you would use Time Intelligence calculations,

I’m going to asume you have a proper Date dimension table in your model and you are visualizing the result by an attribute from that Date table that is in a monthly granularity…

Difference MoM =
VAR cValue = SUM( 'myTable'[amount of unpaid invoices] )
VAR pValue = 
    CALCULATE( 
        SUM( 'myTable'[amount of unpaid invoices] ),
        DATEADD( 'myDates'[Date], -1, MONTH )
    )
VAR Result = pValue - cValue
RETURN
    IF( NOT( ISBLANK( cValue )), Result )

.

If you need further assistance please provide a sample PBIX

3

@RoseyAlexa the question here is how to keep a copy of your data after it’s refreshed, like a snapshot instead of overwriting it.

Typically, you would need to stage your data somewhere for this to work, such as a database, which would get the data then append it to a table with a date stamp of when it was refreshed. Then Power BI can source from that table.

At first I thought it couldn’t be done, and I don’t think it could be automatically through a daily refresh on the service, but you can do it in the PBIX file itself.

In transform data:

  1. On the table add a new column and manually input the date
    image

  2. Now on that table, right-click and choose Duplicate.

  3. On the new table (snapshot table), right-click and uncheck “Include in Report Refresh” – this will hold it steady.

  4. Now you go back to the original version whenever you want to create a snapshot and update that custom column to a new date.

  5. Then on the snapshot table, choose Append Queries and append another snapshot.

Close and apply. Now you can keep a snapshot version of the table within your PBIX – though you will have to manually update it whenever you want a new snapshot kept.

Hope this helps!

3 Likes
4

Thank you Zoe, you understood my dilema exactly.
Your solution is very clever!

If anyone has a way of making this run once a month, instead of a manual update, that’d be the final piece of the puzzle.

2 Likes
5

Hi @RoseyAlexa, did the response provided by @zoe and @Melissa 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.

I hope that you are having a great experience using the Support Forum so far. 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!

6

Hi @RoseyAlexa, we’ve noticed that no response has been received from you since the 21st of April. 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.

7

Hi @RoseyAlexa, due to inactivity, 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.

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!