Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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! 

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

4 Replies

  • rammishra's avatar
    rammishra
    Icon for Helper II rankHelper II
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-kelly-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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!