Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Master calendar to generate fields

Dear All, 

I am new to Power Bi, Looking to generte a master calendar with following fields via DAX or Power Query. 

My requirement:-

Start Date = Min (Date) from the dataset

End Date = Max (Date) from the dataset.

based on the dates from Dataset, i would like to generate below fields for my data analysis. I want to link these dates from the Mastercalendar into my data set like Table A and Table B which has a Date field.

 

Required Fields:

Date --> DD-MM-YYYY

Month-Year --> MMM-YYYY or MMM-YY

Month Name --> MMM

Day --> DD

Quarter --> Q1, Q2, Q3 etc.

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Calendar =
VAR _startdate =
    DATE ( 2023, 1, 1 ) // or, mindate from the fact table
VAR _enddate =
    DATE ( 2024, 12, 31 ) // or, maxdate from the fact table
VAR _t =
    ADDCOLUMNS (
        CALENDAR ( _startdate, _enddate ),
        "Year", YEAR ( [Date] ),
        "Quarter", "Q" & QUARTER ( [Date] ),
        "Month number", MONTH ( [Date] ),
        "Month-Year", FORMAT ( [Date], "mmm-yy" ),
        "Day", FORMAT ( DAY ( [Date] ), "00" ),
        "Date format", FORMAT ( [Date], "dd-mm-yyyy" )
    )
RETURN
    _t

2 Replies

  • sayaliredij's avatar
    sayaliredij
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hello Anonymous ,

     

    Preparing a Calendar Table Based on Start and Stop Dates

    1. Creating the Calendar Table

    • Use the following DAX formula to create a calendar table named "Calendar" that includes all dates between the start and stop dates in your table:
    Calendar = CALENDAR(MIN('Table'[Date]), MAX('Table'[Date]))

    This will create a single column named "Date" containing all dates in the specified range.

    2. Formatting the "Date" Column

    • In the Power BI Desktop ribbon, go to the "Format" tab.
    • Under the "Data Type" group, change the data type of the "Date" column to "Date".
    • Under the "Number" group, choose the desired short date format from the "Format" dropdown menu.

    3. Creating Month-Year and Month Columns (Optional)

    • To create a column named "Month-Year" that displays the month and year in a combined format:
    Month-Year = CONCATENATE(CONCATENATE(FORMAT('Calendar'[Date], "mmmm"), "-"), YEAR('Calendar'[Date]))
    • To create a column named "Month" that displays the month name only:
    Month = FORMAT('Calendar'[Date], "mmmm")

    4. Creating Day and Quarter Columns (Optional)

    • To create a column named "Day" that displays the day number:
    Day = DAY('Calendar'[Date])
    • To create a column named "Quarter" that displays the quarter number:
    Quarter = CONCATENATE("Q", QUARTER('Calendar'[Date]))

    I hope this is helpful!

     

    Thanks and Regards,

    Sayali

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful!! 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Calendar =
VAR _startdate =
    DATE ( 2023, 1, 1 ) // or, mindate from the fact table
VAR _enddate =
    DATE ( 2024, 12, 31 ) // or, maxdate from the fact table
VAR _t =
    ADDCOLUMNS (
        CALENDAR ( _startdate, _enddate ),
        "Year", YEAR ( [Date] ),
        "Quarter", "Q" & QUARTER ( [Date] ),
        "Month number", MONTH ( [Date] ),
        "Month-Year", FORMAT ( [Date], "mmm-yy" ),
        "Day", FORMAT ( DAY ( [Date] ), "00" ),
        "Date format", FORMAT ( [Date], "dd-mm-yyyy" )
    )
RETURN
    _t