Loop between two dates

[Anonymous]

Hello,

I have two dates, and I want to give a number to months from 1 to 26.

The number will be the actual YYYY-MM and the 26th will be (actual YYYY-MM )-(26 month ago)

How can I set a loop and fill the column.

Thanks a lot

[mahoneypat · Accepted solution]

Since you have the year also in the column used for the slicer, you can just create a month index that should work to sort typical month or fiscal month columns (if you don't need to use that column for visuals).  For example,

MonthSort = DATEDIFF(EOMONTH(MIN('Date'[Date]),0), EOMONTH('Date'[Date],0), MONTH)

If you are just going to have fiscal month only, a different approach would be needed.  A couple ways to get just a fiscal month column to sort can be found here.

Solved: How to assign fiscal month number - Microsoft Power BI Community

Regards,

Pat

[littlemojopuppy]

Hi @Anonymous 

Looping isn't possible in DAX.  But that said, this isn't the most elegant solution, but try this...

Playing = 
    VAR DatesInRange =
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] = MAX('Calendar'[Date])
        )
    VAR MonthsBack =
        GENERATESERIES(
            0,
            26,
            1
        )
    VAR Explosion =
        CROSSJOIN(
            DatesInRange,
            MonthsBack
        )
    RETURN

    ADDCOLUMNS(
        Explosion,
        "MonthsPriorDate",
        FORMAT(
            DATEADD(
                'Calendar'[Date],
                -1 * [Value],
                MONTH
            ),
            "YYYY-MM"
        )
    )

You didn't say what your starting date was, so I'm assuming today.

Results...

Hope this helps!  🙂

[mahoneypat]

You can do it in the query editor like this.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  You can replace the #date(  ) part with a paramter with your date value if needed.

let
    Source = List.Transform({0..25}, each Date.AddMonths(#date(2020,1,1),-_)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.ToText([Column1], "yyyy-MM"), type text)
in
    #"Added Custom"

Pat

[mahoneypat]

Here is another approach to consider that gives you columns in your Date table for relative months, weeks, quarters, etc. that you can use in your expressions/visuals.

Power BI Tales From The Front - Day/Week/Month/Quarter/Year Indices - YouTube

Pat

[littlemojopuppy]

@mahoneypat sorry...there were no responses when I started this 🙂

[mahoneypat]

No worries.  I've done the same.  You took a different approach that may better solve this one.

Pat

[Anonymous]

@mahoneypat @littlemojopuppy than you for your response

I think I didn'nt explain well my point.

I have a calendar dimension with dates, years, months and fiscal periods (13). Also I have Text column with the fiscalPeriod-YY.

I use this fiscalPeriod-YY in a slicer as segment for the last 26 periods. I want to sort this FiscalPeriod-YY in my slicer according to my actual date. I think the only way is to set a number to every FiscalPeriod-YY from 1 to 26. 

the regularisation period is set for the last day of the year.

[littlemojopuppy]

@Anonymous not sure I completely understand.  The issue is sorting the YYYY-MM field in chronological order?  Have you tried changing the sort by for that field?