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 dimesion. It should be like below

 

Start of Month DateYearMonthMMM-YY

 

 

Thanks,

PBI V2

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

     

     

     

     

     

6 Replies

  • 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?

  • MichelBikkelaar's avatar
    MichelBikkelaar
    New Member
    9 months ago

    If you have a regular date dimension, with a day for each row, you can create an aggregated table to get a dimension with 1 row per month:

    SUMMARIZE(DayCalendar, DayCalendar[Year],DayCalendar[Month]
    , "First day", MIN(DayCalendar[Date]), "Number of days", COUNTA(DayCalendar[Date]))