Extended Date Table (Power Query M function)

[Melissa]

Hi @richmont and @Keith,

Created a new topic, you can find it here:

Basic Date Table (Power Query M function)

Hi all, There have been requests for a basic Date table that includes week logic centered around the start- / end of week dates AND/OR fiscal weeks. Both will start on the selected Start of week day but the first creates weeks of 7 days when crossing over into the next calendar year and the second weeks with <= 7 days around each fiscal year start and -enddate, as illustrated below. . [image] . Copy this M code script into a new blank query. let fnDateTable = (StartDate as date, EndDate …

I hope this is helpful.

[Keith]

thanks @Melissa

[Melissa]

@richmont

In the ISO code version, what does this line mean? Specifically, the last part in parenthesis.
StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3))

It’s actually a piece of redundant code. Formatted it makes more sence, I think.

From the inside out; I’m getting the weekday name of a specific date, keep its first 3 letters and have that start with a capital.

[richmont]

@Melissa Many thanks! One question. I am getting odd behavior with the Year & Month, Quarter & Month, and Week & Month. I have two models, one for sales, and one for quality issues. The quality issues model includes a direct query dataset (from the sales model), and it is in Mixed mode. When I use those fields on a chart visual, I get a NaN error. I do not get this error on the fully import mode model (the sales model). But I do get it on the mixed mode model (the quality dataset). It only seems to apply to those fields. I can bring in quarter, week, etc with no errors. Any idea what is causing this and how to fix?

[Jose]

Hi @Melissa was concerned about the application of the formula, provided by @sam.mckay in his article about

Showcasing Workday Number in Power BI Using DAX, to your “Date Table”?

When applying it accoding to the fields of your “Date Table” it does not seem to work and I am not able to understand what is wrong.

Thanks in advance!

[Melissa]

Hi @Jose,

Welcome to the Forum!

I’d be happy to look into this for you, can you please create a new Topic, supply your work in progress file with a mock up of the desired outcome.

See this great reference on how to do that.
https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763

[Jose]

Sure! thanks How can i create a new topic, thouhght I had already created one by posting the message?

[DavieJoe]

Hey Jose,

hope all is good with you

You’ve created an additional message/post on an existing thread

If you go to this link Latest topics - Enterprise DNA Forum and select the New Topic button to create a new topic.

[Jose]

Thanks @DavieJoe

[DavieJoe]

Not a problem & welcome to the Forum

[ericet]

Hello Melissa,

In your date table, I think it’s already there but I’m not sure …

What, I’m looking for is workdays in a month.

July 2021
It had 31 days and 22 work days.

I think your Calendar has this already, can you just point me in the right direction please.

Thanks

[BrianJ]

@ericet ,

You can use the IsBusinessDay or IsWorkingDay fields to calculate this. The former is false on holidays and weekends, while the latter is false only on weekends.

With DAX, it’s a simple CALCULATE ( COUNTROWS( Dates), Dates[IsBusinessDay] = TRUE) set against the proper monthly context to give business days per month (or can substitute working days).

If you want to do this in PQ, just use an AllRows/GroupBy combo on Month & Year and IsBusinessDay or IsWorkingDay, and count the rows where the IsBusinessDay or IsWorkingDay is TRUE. This video walks you through that process:

Unleash The Potential of Group By With All Rows

I hope this is helpful.

– Brian

[ericet]

Thank you Brian, that works great. But another question in my data I don’t have all the days in a month
so I cannot count that.

Now, I’m looking at the Extended Date Table and trying to find how many days in a month ?

I’m surprised that I did not see it. Did I miss something ?

How many days in a month ?

[BrianJ]

@ericet ,

Here’s a simple way to do that without having to modify the M code. When you expand this, it will add a Days in Month column to your Dates Table.

– Brian

[ericet]

I looked at your video, but I was looking something even easier

Found this solution: (Still checking if it’s OK at first glance seems to work.)

Number of Days in a Month =
DATEDIFF(
EOMONTH(MAX(atWorkData[YYYY-MM-DD]), -1),
EOMONTH(MAX(atWorkData[YYYY-MM-DD]), 0), DAY
)

[Elitberserk]

Melissa you rock!..Thank you so much!Greetings from Greece!

[HariGanesan]

Hi @Melissa ,
I want to have Latest 4,12,26 and 52 weeks in a slicer which can filter me the data correspondingly from a table. I am not sure how to do it. But I am using our custom table only. Kindly help me with this.

Thanks & Regards,
Hari

[BrianJ]

All,

Here is the full C# script from @AntrikshSharma and @Melissa for automatically sorting the Extended Date Table in Tabular Editor/TE3:

---

// IMPORTANT! requires "unsupported modelling operations" to be enabled. Not supported by MSFT, use at your own risk. 
// Not recommended for use in production models.
var DatesTable = "Dates"; // Change the name between double quotes to your 'default' date table name
var colOperations = new List<Tuple<string, string, bool>>{
            Tuple.Create("Quarter & Year", "QuarternYear", true),
            Tuple.Create("Month Name", "MonthOfYear", true),
            Tuple.Create("MonthShortName", "MonthOfYear", true),
            Tuple.Create("Month Initial", "MonthOfYear", true),
            Tuple.Create("Month & Year", "MonthnYear", true),
            Tuple.Create("DayOfWeekName", "DayOfWeek", true),
            Tuple.Create("Weekday Initial", "DayOfWeek", true),
            Tuple.Create("ISO Quarter", "ISO QuarterOfYear", true),
            Tuple.Create("ISO Quarter & Year", "ISO QuarternYear", true),
            Tuple.Create("Week & Year", "WeeknYear", true),
            Tuple.Create("Fiscal Year", "FiscalYearOffset", false),
            Tuple.Create("Fiscal Quarter", "FQuarternYear", false),
            Tuple.Create("Fiscal Year & Week", "FWeeknYear", false)
        };
foreach(var t in Model.Tables){
    if (t.Name == DatesTable){
        {
            t.DataCategory = "Time"; //Mark as Date table
        }
        foreach (var c in t.Columns){
            for (int i = 0; i < colOperations.Count; ++i)
            if (c.Name == colOperations[i].Item1){
                    c.SortByColumn = t.Columns[colOperations[i].Item2]; //Set Sort By
                    t.Columns[colOperations[i].Item2].IsHidden = colOperations[i].Item3; //Set Hidden
                }
            if (c.DataType == DataType.Double || c.DataType == DataType.Int64){
                c.SummarizeBy = AggregateFunction.None; //Set Summarize By
                c.FormatString = "0"; //Set Format numeric fields
            }
            else if (c.DataType == DataType.DateTime){
                c.FormatString = "Short Date"; // Set Format date fields
            }
        }
    }
}

[Fcovey]

Thank you for sharing Melissa’s extended date table code. Unfortunately, every time I refresh all the data set in my model, I receive the following “errors in dates” message. This creates several error tables which I cannot figure out. Has anyone else encountered this issue? What am I doing incorrectly? Thank you in advance.

[Melissa]

Hi @Fcovey,

Welcome to the forum.
Can you share that file? The supplied pictures don’t provide enough for me to go on…

Thanks!