PUG Exchange

Expand all | Collapse all

Calendar - DAX or M

  • 1.  Calendar - DAX or M

    Bronze Contributor
    Posted 6 days ago
    Greetings All!

    When you need to get a calendar, how do you prefer to do it?

    Years ago I used to get one from the Azure Data Marketplace. But Microsoft closed it, and I was forced to build one in M.

    Since then, I have dabbled with building a calendar in DAX.

    How do you like to do it? Why do you prefer one instead of the other?

    Thanks,

    ------------------------------
    Andy Parkerson
    Web Developer
    ShoppersChoice.com
    Baton Rouge LA
    ------------------------------


  • 2.  RE: Calendar - DAX or M

    Posted 5 days ago
    Hi Andy,
    I do it in M simply because that's where I found the most useful code, and also, not all of my models need DAX and so it keeps things consistent between clients.

    ------------------------------
    Ben Howard
    ------------------------------



  • 3.  RE: Calendar - DAX or M

    Posted 5 days ago
    Where can I find code samples for M calendars?

    ------------------------------
    Carolee Heynen
    SharePoint Admin
    Korn Ferry
    North Hollywood CA
    616 516 6739
    ------------------------------



  • 4.  RE: Calendar - DAX or M

    Posted 5 days ago
    I thought it would be worth publishing this.

    Creating a Date Table in Power Query - Applepark
    Applepark remove preview
    Creating a Date Table in Power Query - Applepark
    Creating a Date Table in Power Query Simply because this asked for recently, here's some M-code that you can paste into Power Query to create a data calendar - please note no warranties etc are implied by publishing this code. /* Power Query...
    View this on Applepark >





    ------------------------------
    Ben Howard
    ------------------------------



  • 5.  RE: Calendar - DAX or M

    Posted 5 days ago
    I found this M code helpful and built a variation of it: Generating A Date Dimension Table In Power Query
    Chris Webb's BI Blog remove preview
    Generating A Date Dimension Table In Power Query
    There are hundreds of different methods for generating a Date dimension table for use in Power Pivot: you can use TSQL, Excel formulas, import from the DateStream table in the Windows Azure Marketplace, and there's even an app for it. Nevertheless I thought it would be a good idea to solve this problem in Power...
    View this on Chris Webb's BI Blog >

    Best of luck!

    ------------------------------
    Danny Dennison, MCSA
    http://bit.ly/MCSA_BI
    Business Intelligence Analyst
    Reynolds Lake Oconee
    Greensboro, GA
    7064671624
    ------------------------------



  • 6.  RE: Calendar - DAX or M

    Posted 5 days ago
    I use a date calendar that I got from Enterprise DNA  enterprisedna.co

    Sam - the owner - gives the code away.

    ------------------------------
    Guy Johnson
    Network Administration
    Interstate Chemical Company
    Hermitage PA
    7248131186
    ------------------------------



  • 7.  RE: Calendar - DAX or M

    Posted 5 days ago
    Hi Andy,

    Marco Russo from SQLBI has released a fairly comprehensive DAX date table.

    Have a look at https://www.sqlbi.com/tools/dax-date-template/
    Kind regards,
    Andrew

    ------------------------------
    Andrew Exley
    BI and Data Analytics Consultant
    Adelaide, Australia
    ------------------------------



  • 8.  RE: Calendar - DAX or M

    Bronze Contributor
    Posted 5 days ago
    So does anybody use DAX to create a calendar table?

    If so, why do you do it that way instead of with Power Query (M)?

    Thanks,

    ------------------------------
    Andy Parkerson
    Web Developer
    ShoppersChoice.com
    Baton Rouge LA
    ------------------------------



  • 9.  RE: Calendar - DAX or M

    Posted 5 days ago
    Count me as a DAX fan for creating calendars.

    Date = ADDCOLUMNS (
    CALENDAR ( "1-jan-2016", today() ),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ))

    ------------------------------
    Mike Hammons
    Director, Business Intelligence
    AKA Enterprise Solutions

    770-926-5020
    ------------------------------



  • 10.  RE: Calendar - DAX or M

    Posted 4 days ago
    @Mike Hammons​, another advantage of DAX calendar is dynamic range... first/last date in fact table.

    ------------------------------
    Danny Dennison, MCSA
    http://bit.ly/MCSA_BI
    Business Intelligence Analyst
    Reynolds Lake Oconee
    Greensboro, GA
    7064671624
    ------------------------------



  • 11.  RE: Calendar - DAX or M

    Posted 5 days ago
    If I don't have a Database, then I would use Power Query and M - start with a LIST of Dates and convert to a TABLE, here is a reference from Excel Guru Ken Puls:

    https://www.powerquery.training/portfolio/dynamic-calendar-table/

    I've also attached a Dynamic Calendar created by Reid Havens at PowerPivotPro, it uses parameters


    ------------------------------
    Kristine Dyess
    Principal Consultant
    PowerPivotPro
    SURPRISE AZ
    4804374488
    ------------------------------



  • 12.  RE: Calendar - DAX or M

    Posted 4 days ago
    Any idea on how to make either DAX or M work for a Fiscal year?
    i.e. October 1 is the first day of the year?

    I am sure very few companies work only on a calendar year.



    ------------------------------
    Tommy Griffin
    Software Administrator, Data Analyst
    Tullahoma TN
    ------------------------------



  • 13.  RE: Calendar - DAX or M

    Posted 3 days ago
    ​One more suggestion: The guys at sqlbi.com have created a pbix calendar template that you can download and insert into your reports.

    DAX Date Template - SQLBI
    Sqlbi remove preview
    DAX Date Template - SQLBI
    DAX Date Template is a Power BI template file containing a fully-featured Date table. This template spares the user from writing the required DAX expression
    View this on Sqlbi >


    ------------------------------
    Sara Larkin
    Technical Business Analyst
    Bank Data Warehouse
    BOKF Tulsa
    ------------------------------



  • 14.  RE: Calendar - DAX or M

    Posted 3 days ago
    In DAX, you can use CALENDARAUTO function (CALENDARAUTO Function (DAX) | Microsoft Docs ) . This function allows you to create a list of dates starting with the fist day of the Fiscal year. The function sets the MinDate as the earliest date in the model which is not in a calculated column or calculated table and the max date as latest date in the model which is not in a calculated column or calculated table. The full date range will be all dates between the beginning of the fiscal year associated with MinDate and the end of the fiscal year associated with MaxDate.

    ------------------------------
    Andrea Letourneau
    BI Consultant
    AZ
    ------------------------------



  • 15.  RE: Calendar - DAX or M

    Posted 3 days ago
    Hi @Tommy Griffin

    Yes, you can add Fiscal offsets to the calendar like below. In this example the I am starting Q1 and the Fiscal Year in April....this should get you started on changing it to October.

    Date =
    ADDCOLUMNS (
    CALENDAR ( "1-jan-2016", today() ),
    "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "Monthnumber", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
    "FiscalYear" , CONCATENATE("FY",
    IF(MONTH([Date]) <=3,
    VALUE(FORMAT([Date],"YY")),
    VALUE(FORMAT([Date],"YY")) +1)
    ),
    "FiscalQtr", SWITCH( TRUE(),
    MONTH([Date]) = 1, "Q4",
    MONTH([Date]) = 2, "Q4",
    MONTH([Date]) = 3, "Q4",
    MONTH([Date]) = 4, "Q1",
    MONTH([Date]) = 5, "Q1",
    MONTH([Date]) = 6, "Q1",
    MONTH([Date]) = 7, "Q2",
    MONTH([Date]) = 8, "Q2",
    MONTH([Date]) = 9, "Q2",
    MONTH([Date]) = 10, "Q3",
    MONTH([Date]) = 11, "Q3",
    "Q3" ),
    "FiscalMoYr",CONCATENATE(FORMAT([Date],"MMM"),FORMAT([Date]," YYYY")),
    "FiscalQtrYr",CONCATENATE(SWITCH( TRUE(),
    MONTH([Date]) = 1, "Q4",
    MONTH([Date]) = 2, "Q4",
    MONTH([Date]) = 3, "Q4",
    MONTH([Date]) = 4, "Q1",
    MONTH([Date]) = 5, "Q1",
    MONTH([Date]) = 6, "Q1",
    MONTH([Date]) = 7, "Q2",
    MONTH([Date]) = 8, "Q2",
    MONTH([Date]) = 9, "Q2",
    MONTH([Date]) = 10, "Q3",
    MONTH([Date]) = 11, "Q3",
    "Q" ),FORMAT([Date]," YYYY")
    ))

    ------------------------------
    Mike Hammons
    Director Field Service
    AKA Enterprise Solutions

    ------------------------------



  • 16.  RE: Calendar - DAX or M

    Posted 3 days ago
    Thanks, Andrea and Mike both suggestions helped a lot.

    ------------------------------
    Tommy Griffin
    Software Administrator, Data Analyst
    Tullahoma TN
    ------------------------------