Creating month tabe

[KJay]

Hi everyone,

I need to create a month table as in starting from Jan-2019 to Dec-2019.Only those 12 rows should be there. I tried calendar function but it gave all the 365 days. I only need the months. May be for the date it's okay to get the month end day only.

Could someone please help me on this?

[amitchandak]

Please refer

https://community.powerbi.com/t5/Desktop/Calendar-w-months-interval/td-p/232049

[KJay]

Thank you for your reply. I tried this earlier. But it gives the output as a text. I'm unabe to make relationship with another table which actually have dates.

[v-piga-msft]

Hi @KJay ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[v-piga-msft · Accepted solution]

Hi @KJay ,

I think you could create the calendar table with Power Query like below.

let
    StartDate= #date(2019,1,1),     // Change start date  #date(yyyy,m,d)   
    EndDate = #date(2019,12,31),  // Could change to #date(yyyy,m,d) if you need to specify future date

    DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}),
    #"Long Month Name" = Table.AddColumn(#"Changed Type", "Month Long", each Date.MonthName([Date]), type text),
    #"Removed Duplicates" = Table.Distinct(#"Long Month Name", {"Month Long"})
in
    #"Removed Duplicates"

Here is the output.

Then you could show the date format like this in power bi.

Best Regards,

Cherry

[amitchandak]

In case you have tables both month and date level. Please create a field with month start or end date based on month and year and join with the time dimension having all dates.

In case everything is at month level then you create a month start date or month-end date and join with them. Both fact and dim can dates or varchar month format which matches 

Month Date = date(MonthTable[Year],MonthTable[Month Number],"01")

Month End Date = EOMONTH(date(MonthTable[Year],MonthTable[Month Number],"01"),0)