Forum Discussion

eggwald's avatar
eggwald
Frequent Visitor
2 years ago
Solved

Month List Table

Hello, 

 

I am really struggling on this. It seems like it should be easy, but I think I am missing something! 🙂

 

I need a table with a list of months starting April 2020 to today and have it automatically update with the new months as time goes on. 

 

Any ideas?

 

Cheers

Ed

Need Help
  • BA_Pete's avatar
    BA_Pete
    2 years ago

    Hi Ed,

     

    Try this Power Query solution:

    let
    Source =
    List.Distinct(
        List.Transform(
            { Number.From(#date(2020,4,1))..Number.From(Date.From(DateTime.LocalNow())) },
            each Text.Combine(
                {
                   Text.Start(Date.MonthName(Date.From(_)), 3),
                   Text.End(Text.From(Date.Year(Date.From(_))), 2)
                }, "-"
            )
        )
    ),
    convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    convertToTable

     

    For this output:

     

    Pete

  • eggwald's avatar
    eggwald
    2 years ago

    months = var t1 = CALENDAR(DATE(2020, 4, 1), TODAY()) var t2 = ADDCOLUMNS(t1, "time_y", format([Date], "yyyy"), "time_ym", format([Date], "yyyy-MM") ) var t3 = GROUPBY(t2, [time_y], [time_ym], "d1", minx(CURRENTGROUP(), [Date]), "d2", maxx(CURRENTGROUP(), [Date])) return t3

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    Hi Ed,

     

    Try this Power Query solution:

    let
    Source =
    List.Distinct(
        List.Transform(
            { Number.From(#date(2020,4,1))..Number.From(Date.From(DateTime.LocalNow())) },
            each Text.Combine(
                {
                   Text.Start(Date.MonthName(Date.From(_)), 3),
                   Text.End(Text.From(Date.Year(Date.From(_))), 2)
                }, "-"
            )
        )
    ),
    convertToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    convertToTable

     

    For this output:

     

    Pete

    • eggwald's avatar
      eggwald
      Frequent Visitor
      2 years ago

      months = var t1 = CALENDAR(DATE(2020, 4, 1), TODAY()) var t2 = ADDCOLUMNS(t1, "time_y", format([Date], "yyyy"), "time_ym", format([Date], "yyyy-MM") ) var t3 = GROUPBY(t2, [time_y], [time_ym], "d1", minx(CURRENTGROUP(), [Date]), "d2", maxx(CURRENTGROUP(), [Date])) return t3

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    2 years ago

    Hi, eggwald 

     

    use calender()

    create a new table in pbi desktop and pasete this dax

    CalendarTable = CALENDAR(DATE(2020, 4, 1), TODAY())