Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Power BI - Month Dimension

Hello All,   We have a requirement to create a Month Dimension similar to date dimension which should have only start of month date instead of all the dates of month. How to create such Month dimes...
Need Help
  • eliasayyy's avatar
    eliasayyy
    3 years ago

    pelase try Anonymous 

     

     

    let
    CurrentYear = Date.Year(DateTime.LocalNow()),
    EndDate = #date(CurrentYear,12,31),
    StartDate = #date(CurrentYear,1,1),
    NumberOfMonths = (Date.Year(EndDate) - CurrentYear + 1) * 12,
    MonthStarts = List.Transform({0..(NumberOfMonths-1)}, each Date.StartOfMonth(Date.AddMonths(StartDate, _))),
    
    // Convert the list to a table
    TableFromList = Table.FromList(MonthStarts, Splitter.SplitByNothing()),
    RenameDateColumn = Table.RenameColumns(TableFromList,{{"Column1", "Date"}}),

    // Add Year, Month Number and Month-Year columns
    Addedyear = Table.AddColumn(RenameDateColumn, "Year", each Date.Year([Date])),
    AddMonthNumber = Table.AddColumn(Addedyear, "Month Number", each Date.Month([Date])),
    AddMonthYear = Table.AddColumn(AddMonthNumber, "Month-Year", each Date.ToText([Date], "MMM-yy")),

    // Change the types of all columns
    ChangeTypes = Table.TransformColumnTypes(
        AddMonthYear, 
        {
            {"Date", type date},
            {"Year", Int64.Type},
            {"Month Number", Int64.Type},
            {"Month-Year", type text}
        }
    )
in
    ChangeTypes

     

     

     

     

     

eliasayyy's avatar
eliasayyy
Icon for Memorable Member rankMemorable Member
3 years ago

hello Anonymous 

 

 

Start Of Month = 
EOMONTH('Table'[Date],-1) +1 
OR
STARTOFMONTH('Table'[Date])

Year = YEAR('Table'[Date])

Month = MONTH('Table'[Date])

Month - Year(MMM-YY) = FORMAT('Table'[Date],"MMM-YY")

 

 

 

 

 



  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello eliasayyy ,

    Thanks for your response.  Actually, we are looking for generating Month dimesion based on start Year & End Year (Current Year).. It should keep add the data once next year comes. We have tried with Advance Editor like below code:
    let
    StartDate = #date(StartYear,1,1),
    EndDate = #date(Date.Year(DateTime.LocalNow()),12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),

     

    but the issue with above code is that it is generating all the dates of months. We want only Start of Month date.

    Thanks,

    PBI V2

    • eliasayyy's avatar
      eliasayyy
      Icon for Memorable Member rankMemorable Member
      3 years ago

      i didnt understand you need a code to build a celandar table base don start of year and end of year of the current yeyar? 


      so the list should be from jan 1 2023 to december 31 2023?

    • eliasayyy's avatar
      eliasayyy
      Icon for Memorable Member rankMemorable Member
      3 years ago

      pelase try Anonymous 

       

       

      let
    CurrentYear = Date.Year(DateTime.LocalNow()),
    EndDate = #date(CurrentYear,12,31),
    StartDate = #date(CurrentYear,1,1),
    NumberOfMonths = (Date.Year(EndDate) - CurrentYear + 1) * 12,
    MonthStarts = List.Transform({0..(NumberOfMonths-1)}, each Date.StartOfMonth(Date.AddMonths(StartDate, _))),
    
    // Convert the list to a table
    TableFromList = Table.FromList(MonthStarts, Splitter.SplitByNothing()),
    RenameDateColumn = Table.RenameColumns(TableFromList,{{"Column1", "Date"}}),

    // Add Year, Month Number and Month-Year columns
    Addedyear = Table.AddColumn(RenameDateColumn, "Year", each Date.Year([Date])),
    AddMonthNumber = Table.AddColumn(Addedyear, "Month Number", each Date.Month([Date])),
    AddMonthYear = Table.AddColumn(AddMonthNumber, "Month-Year", each Date.ToText([Date], "MMM-yy")),

    // Change the types of all columns
    ChangeTypes = Table.TransformColumnTypes(
        AddMonthYear, 
        {
            {"Date", type date},
            {"Year", Int64.Type},
            {"Month Number", Int64.Type},
            {"Month-Year", type text}
        }
    )
in
    ChangeTypes

       

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hello eliasayyy 

        This is really a great help. The solution provided by you is working for us. Thank you so much...  

        Thanks,

        PBI V2