Master calendar to generate fields

[Anonymous]

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.

[Jihwan_Kim · Accepted solution]

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

[sayaliredij]

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!!