Month List Table

[eggwald]

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

[BA_Pete · Accepted solution]

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

[BA_Pete · Accepted solution]

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 · Accepted solution]

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]

Hi, @eggwald 

use calender()

create a new table in pbi desktop and pasete this dax

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

[eggwald]

Thanks @rubayatyasmin, is there a way to get it to just show the months?

EG: 

Apr-20

May-20

Jun-20

Jul-20

....etc

[rubayatyasmin]

@eggwald 

from the data view select the column and check for format option in the ribon. Select your desired format for that. Or use FORMAT DAX function. 

refer: https://learn.microsoft.com/en-us/dax/format-function-dax