Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kaushi2020
Regular Visitor

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.

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1707634940864.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1707634940864.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


sayaliredij
Super User
Super User

Hello @kaushi2020 ,

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.