Automate table to add months

[Anonymous]

Good morning everyone - 

I am having the damndest time trying to automate a data set to automatically add a new month. I'm not even sure if this is possible.  Below is a screenshot of a data table I will be entering into PowerBI.  We have several locations in my business which have different rates that can change in any given month.  What I am looking for is a measure which will automatically add a new month, with the previous month's rates. 

So for example, when we get to October 1st, 2021, the measure would automatically add "Oct-21" in cell A11,, as well as the September values for the 3 locations (70, 90, 100).  If there is a rate change, I can go in and manually adjust, but if there is not a rate change, I'd like for the NEW month to just use the previous month's values.

Thank you so much for your help! 

[rammishra]

Interesting question. I am afraid, it is not possible in Power BI- if the intention is to insert rows in the database. We can create a Measure that probably can do what you want in a matrix (a visual). 

[Anonymous]

Hi Rammishra - 

My other thought is that I could add months in my table out for a couple years with the current values (70, 90, 100), and I would manually edit the rates if we have a rate change.  But I'm not sure in the visual, how I would be able to show through the current month? So for example, when October 1st, 2021 rolls around, I would want my visual to show all the rates for the previous months THROUGH October (but nothing beyond).  I know I can manually add October in the filters, but how would I automate it so that PowerBI knows to only show the rates through the current month?

Thank you! 

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Suppose your data is till August,such as below:

First create a calendar table to the end of the year,then create a relationship between the 2 tables;

Finally create a measure as below:

Measure =
IF ( MONTH ( MAX ( 'Table 2'[Date] ) ) <= MONTH ( TODAY () ), 1, BLANK () )

Put the measure in the filter pane and select measure is 1:

 And you will see:

For each new adding month,you could use another measure to fill  in the data.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!

[Anonymous]

Thank you, Kelly!