Expanding Datasheet

[vwiles84]

I have a spreadsheet that has budget numbers for the month broken out by location. The budgets have changed effective 5/30 and instead of updating the existing number by location, I need to preserve the previous number for historical look back purposes.  I am trying to find the best way to build the spreadsheet via power query so that power BI can have both sets of budget number so a single date slicer on the report can be used used.  The budget numbers are only different beginning 5/30, and all previous days should be the original number.  Any suggestions on how to setup the query to include the past and present/future numbers?

[BA_Pete]

Hi @vwiles84 ,

I would add the date of the first of the month to each relevant row.

This will then relate easily to a calendar table and give you a time-intelligent record for the ages, but this largely depends on what format/structure the data is currently in.

If you can post an example of the existing table data/structure I should hopefully be able to give you column calculation that will apply the correct date marker dynamically.

Pete

[vwiles84]

Sure thing. Here is a sample of the data

[BA_Pete · Accepted solution]

@vwiles84 ,

Ok. So your old budget rows you want to have start date 01/01/1900 or whatever is a realistic start date (be prudent here, as you may want to expand this table into individual dates further down the line - see below).

You then want an end date field that is 05/29/2021. Keep all this in the same table as your new budget rows which, obviously, have start date 05/30/2021. For the new budget rows end date, you can either leave this blank until you change the budgets again, or use something like =TODAY().

You now have what's called a Slowly Changing Dimension (SCD) table.

There's a few ways to deal with SCD's in Power BI. One involves using surrogate keys which I'll let you do your own research on as it's probably outside of scope for your scenario. The two main ones that I would use would be:

1) Always code the date range you want to use into your measures, something like this:

_budget =
CALCULATE(
  SUM(budget([daily]),
  FILTER(
    budget,
    budget[Start Date] >= MIN(calendar[Date])
    && budget[End Date] <= MAX(calendar[Date])
  )
)

 Not a perfect/working example, but you get the picture.

2) Expand your SCD into individual date values that can be related directly to your calendar table. In Power Query, you would add a custom column something like this, then expand it:

List.Transform(
  {Number.From([Start Date])..Number.From([End Date])},
  each Date.From(_)
)

Option two is usually my preferred option as it simplifies the modelling and measure creation process and avoids slow calculation times. HOWEVER, depending on the size of your SCD, and the range of dates you are covering, this can result in HUGE tables, which may slow down report refreshes. Remember you can filter the resultant date list in Power Query to just the range of dates you need to reduce the rows being applied to the model.

Hope this makes sense.

Pete

[vwiles84]

Thanks so much. I am going to try the options provided and will report back. 

[vwiles84]

The 2nd option provided me the solution I needed. Thanks again for your help.