How to create a loop for once every month on the 1st to add a new query

[Brite]

Hello,

I am having to edit this DAX formula every month on the 1st. Is there a way to create a loop so that on the first of every month it plugs in current data and adds one month for every loop: Month_12 = if(Month(Today()="[CURRENT MONTH]" && Year(TODAY()="[CURRENT YEAR]", calculate(Divide([Month 12],[Month 1]), 'Calendar BV' [MonthYear] = "May-22", 'Calendar BV' [MonthYear] ="June-22"...         --> +1 month for every loop that is made once a month

Any help is greatly appreciated!!

Month 2_% = if(MONTH(TODAY())=4 && YEAR(TODAY())=2022, calculate(Divide([Month 2 N],[Month 1]), 'Calendar BV'[MonthYear] = "May-21" ||  'Calendar BV'[MonthYear] = "Jun-21" ||  'Calendar BV'[MonthYear] = "Jul-21"  || 'Calendar BV'[MonthYear] = "Aug-21" ||  'Calendar BV'[MonthYear] = "Sep-21" ||  'Calendar BV'[MonthYear] = "Nov-21" || 'Calendar BV'[MonthYear] = "Dec-21" ||  'Calendar BV'[MonthYear] = "Jan-22"  ||  'Calendar BV'[MonthYear] = "Feb-22"), 
if(MONTH(TODAY())=5 && YEAR(TODAY())=2022, calculate(Divide([Month 2 N],[Month 1]), 'Calendar BV'[MonthYear] = "May-21" ||  'Calendar BV'[MonthYear] = "Jun-21" ||  'Calendar BV'[MonthYear] = "Jul-21"  || 'Calendar BV'[MonthYear] = "Aug-21" ||  'Calendar BV'[MonthYear] = "Sep-21" ||  'Calendar BV'[MonthYear] = "Nov-21" || 'Calendar BV'[MonthYear] = "Dec-21" ||  'Calendar BV'[MonthYear] = "Jan-22"  ||  'Calendar BV'[MonthYear] = "Feb-22" || 'Calendar BV'[MonthYear] ="Mar-22"), 
if(MONTH(TODAY())=6 && YEAR(TODAY())=2022,  calculate(Divide([Month 2 N],[Month 1]), 'Calendar BV'[MonthYear] = "May-21" ||  'Calendar BV'[MonthYear] = "Jun-21" ||  'Calendar BV'[MonthYear] = "Jul-21"  || 'Calendar BV'[MonthYear] = "Aug-21" ||  'Calendar BV'[MonthYear] = "Sep-21" ||  'Calendar BV'[MonthYear] = "Nov-21" || 'Calendar BV'[MonthYear] = "Dec-21" ||  'Calendar BV'[MonthYear] = "Jan-22"  ||  'Calendar BV'[MonthYear] = "Feb-22" || 'Calendar BV'[MonthYear] ="Mar-22" || 'Calendar BV'[MonthYear] ="Apr-22"), 
if(MONTH(TODAY())=7 && YEAR(TODAY())=2022,  calculate(Divide([Month 2 N],[Month 1]), 'Calendar BV'[MonthYear] = "May-21" ||  'Calendar BV'[MonthYear] = "Jun-21" ||  'Calendar BV'[MonthYear] = "Jul-21"  || 'Calendar BV'[MonthYear] = "Aug-21" ||  'Calendar BV'[MonthYear] = "Sep-21" ||  'Calendar BV'[MonthYear] = "Nov-21" || 'Calendar BV'[MonthYear] = "Dec-21" ||  'Calendar BV'[MonthYear] = "Jan-22"  ||  'Calendar BV'[MonthYear] = "Feb-22" || 'Calendar BV'[MonthYear] ="Mar-22" || 'Calendar BV'[MonthYear] ="Apr-22" || 'Calendar BV'[MonthYear] = "May-22"), Blank()))))  

[Brite]

@v-stephen-msftThank you! This works great

[v-stephen-msft · Accepted solution]

Hi @Brite ,

Are you deliberately missing Oct-21?

If so, you could try creating below measure if there's a date field in your 'Calendar BV'.

Month 2_% =
VAR _START =
    DATE ( 2021, 5, 1 )
VAR _NUMBER =
    MONTH ( TODAY () ) - 4
VAR _END =
    EOMONTH ( _start, 9 + _NUMBER )
RETURN
    CALCULATE (
        DIVIDE ( [Month 2 N], [Month 1] ),
        FILTER (
            'Calendar BV',
            [Date] >= _START
                && [Date] <= _END
                && MONTH ( [Date] ) <> 10
                && YEAR ( [Date] ) <> 2021
        )
    )

If you accidentally miss October, delete the October 21 filter statement.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.