You might be wondering, didn’t we create a Perpetual Holiday Calendar already? And the answer is, yes we sure did! That was eDNA’s maiden POTW DAX Challenge and now we get to do it all over again using only Power Query/M only.
Creating a holiday table is critical for a number of time intelligence calculations, most commonly calculating net workdays. For this problem’s solution, Melissa will show you how to take this mix of specific and relative dates for US holidays, and create a Holiday table in Power Query with Dates for each holiday, dynamically for all Years included in the Dates table.
The Problem of the Week initiative is open to members and non-members with the goal to improve Power BI skills through practical application. For more info about Problem of the Week, check out this post:
https://forum.enterprisedna.co/t/new-enterprise-dna-initiative-sneak-preview-problem-of-the-week/12504?u=enterprisedna
***** Video Details *****
00:00 Introduction
01:33 Holiday table
02:05 Retrieve years from date table
02:38 Month number
03:17 Numeral from day column
03:57 Date name with List.PositionOf
04:38 Replacing -1 values with null
06:11 List.Select
06:34 Conditional logic
07:34 List.Skip
08:16 RAW Holidays table
08:20 Return list with dates
09:25 Celebrated on date
09:57 Change types
***** Related Links *****
Intro to Problem of the Week #12 – https://forum.enterprisedna.co/t/problem-of-the-week-12-pq-perpetual-holiday-calendar/16800?u=enterprisedna
Intro to Problem of the Week #1 – https://youtu.be/6CRmldbomeU
***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE – Ultimate Beginners Guide To DAX – http://portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
FREE – Power BI Resources – http://enterprisedna.co/power-bi-resources
FREE – 60 Page DAX Reference Guide Download – https://enterprisedna.co/dax-formula-reference-guide-download/
Enterprise DNA Membership – https://enterprisedna.co/membership
Enterprise DNA Online – http://portal.enterprisedna.co/
Enterprise DNA Events – https://enterprisedna.co/enterprise-dna-events-page/
#EnterpriseDNA #PowerBI #ProblemOfTheWeek #PowerBIDesktop #PowerBITutorial #PowerQuery #PowerQueryTutorial #MCode #PerpetualCalendar