cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Automate table to add months

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! 

 

Automate Data.jpg

1 ACCEPTED SOLUTION

Hi  @Anonymous ,

 

Suppose your data is till August,such as below:

vkellymsft_0-1631782568155.png

 

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:

vkellymsft_1-1631782684044.png

 

 And you will see:

vkellymsft_2-1631782706845.png

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!

View solution in original post

4 REPLIES 4
rammishra
Helper I
Helper I

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
Not applicable

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! 

Hi  @Anonymous ,

 

Suppose your data is till August,such as below:

vkellymsft_0-1631782568155.png

 

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:

vkellymsft_1-1631782684044.png

 

 And you will see:

vkellymsft_2-1631782706845.png

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
Not applicable

Thank you, Kelly!

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.