Expanding Datasheet

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