Forum Discussion

Nimai123's avatar
Nimai123
Post Patron
6 years ago

Automate the M query

My data source is SAP BW and I have 12 years of data in it, so I have to apply the filter and extract the data.

 

The filter is the starting line of the code.

 

{Cube.ApplyParameter, "[0I_CALMO]", {"[0CALMONTH].[201908]", "[0CALMONTH].[202008]"}},
 
In the above filter, I have used a start month and end month. I need to make the end month filter ("[0CALMONTH].[201708]"}} ) Automate in the manner when at every 5th of the month the filer should change with the current month and the start month filter should be the same because now I have to do it manually every month for more than 20 reports. 
 
I tried using the variable but it was not working, I am not an expert with M language, can anyone guide me on how to achieve this or is this even possible?
 
For example, the query should become on 5 September.
{Cube.ApplyParameter, "[0I_CALMO]", {"[0CALMONTH].[201908]", "[0CALMONTH].[202009]"}},
 
Thanks in Advance

 

Need Help

7 Replies

    • Nimai123's avatar
      Nimai123
      Post Patron
      6 years ago

      Hey Greg_Deckler 

      Example

      Current month query for August 

       

      Start Date "[0CALMONTH].[201908]"

      End Date "[0CALMONTH].[202008]"      -----  August - 2020

       

      {Cube.ApplyParameter, "[0I_CALMO]", {"[0CALMONTH].[201908]", "[0CALMONTH].[202008]"}},

       

      Required Query to be in September Automatically.

       

      Start Date "[0CALMONTH].[201908]"

      End Date "[0CALMONTH].[202009]"      -----  September - 2020

       

      {Cube.ApplyParameter, "[0I_CALMO]", {"[0CALMONTH].[201908]", "[0CALMONTH].[202009]"}},

       

      ImkeF 

      Pragati11 

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi Nimai123 ,

         

        Create a query as below:

         

          #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "End date", each Date.AddMonths([Date],13))

         

        And you will see:

         

        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!