Tag - DAX

Creating Custom Calendars in Power BI
PowerBI
Creating Custom Calendars in Power BI
Nirav Dangar
Mar 08, 2024

Introduction Have you ever tried to add holidays which lie between today (or any dates) to a specific date or the same kind of scenario? If yeah Then this article helps you   Scenario Recently, I faced a situation where I needed to add days to the current date (today) which depends on the total number of hours left for a particular employee. Means employee A has 150 hours left and spent only 6-hour for one working day, then divide the hour by daily spend and find the date (forecast date) where employee A completed their task but did not include holidays and weekends. Let’s consider employee X. Get 15 fore-cast days on the basis of the hours left. Those 15 days do not include weekends and holidays, which we need to add to the current date (today). Let’s consider Employee X got 3 days of weekend or public holiday out of 15 days, then it also needs to add in fore-cast days, which means now 15 days become 18 days.  If employee X's forecast date falls on a weekend or a holiday, it is also not applicable.   We take some data for solving scenario   Holiday Table   Logic behind solving this problem is we going to take a separate date table which will help for deriving fore-cast date and add new column of Holidays. After that we add a new column which give us a flag of 0 if Date in weekends or in holidays and 1 for date in working day after that we give rank according to flag with exclude of Flag 0.   We make a new date Table which particular Use for Fore-cast Date Using Below DAX Code Here I am taking only 2024 Date field fore-cast Datetable = ADDCOLUMNS( CALENDAR( TODAY(), DATE(2024,12,31) ), "weekday", WEEKDAY([Date],2), "Dayname", FORMAT([Date],"dddd") )   Join holiday Table and fore-cast Date table using Date field     Make a new column (Holiday) in Fore-cast Date table which help us to bring only holiday date from holiday table to existing table holiday = RELATED('Holiday Table'[holiday date])   Make another new column with some condition using Dax code which give Flag 0 for those date which are weekends and holidays and 1 for Working days. workingday = IF( NOT('fore-cast Datetable'[weekday]) in {6,7}, IF( 'fore-cast Datetable'[holiday]<>BLANK(), 0, 1 ), 0 ) Add column Which Give day no from Today with ignoring Weekends and holidays Day Number = RANKX( FILTER( ALL('fore-cast Datetable'[Date],'fore-cast Datetable'[workingday]), 'fore-cast Datetable'[workingday]<>0 ), 'fore-cast Datetable'[Date],,ASC )   Our main goal is taking Days on behalf of Hours and find those date which is equivalent to left days from today with ignoring weekends and holidays. Day Number column give us that ability   Fore-cast date table Look like below Now make a new column in employee table Forecast date column = var a =FLOOR((Employee[Hour left]/6),1) return MAXX( FILTER( ALL('fore-cast Datetable'[Date],'fore-cast Datetable'[Day Number]), 'fore-cast Datetable'[Day Number]=a ), 'fore-cast Datetable'[Date] )   We can make measure with little bit changes fore-cast date using measure = var a = FLOOR(SUM(employee[hour left])/6,1) return MAXX( FILTER( ALL('fore-cast Datetable'[Date],'fore-cast Datetable'[Day Number]), 'fore-cast Datetable'[Day Number]=a ), 'fore-cast Datetable'[Date] )   Make a table visual     If you don’t want to add new table to your Existing data model We can also achieve Without fore-cast date table by creating it on fly.   Use below Dax code for column fore-cast date without table refrence = var a = FLOOR(('Employee'[Hour left]/6),1) var b =NETWORKDAYS(TODAY(),TODAY()+a,1) var c = ADDCOLUMNS( CALENDAR(TODAY(),TODAY()+a+b), "weekday", WEEKDAY([Date],2), "dayname",FORMAT([Date],"dddd"), "publikholiday",LOOKUPVALUE('Holiday Table'[Holiday Date],[holiday date],[Date]), "holidaycondition", var a1 = IF( not(WEEKDAY([Date],2)) in {6,7}, IF( LOOKUPVALUE('Holiday Table'[Holiday Date],[holiday date],[Date])<>BLANK(), 0, 1 ), 0 ) RETURN a1 ) var f = ADDCOLUMNS(c,"rank",RANKX(FILTER(c,[holidaycondition]<>0),[Date],,ASC)) var e= MINX(FILTER(f,[rank]=a),[Date]) return e here little bit changes in above code for measure Use below code for measure fore-cast date measurewithout table refrence = var a = FLOOR(sum(employee[hour left])/6,1) var b =NETWORKDAYS(TODAY(),TODAY()+a,1) var c =ADDCOLUMNS( CALENDAR(TODAY(),TODAY()+a+b), "weekday",WEEKDAY([Date],2), "dayname",FORMAT([Date],"dddd"), "publikholiday",LOOKUPVALUE('Holiday Table'[Holiday Date],'Holiday Table'[holiday date],[Date]), "holidaycondition", var a1 = IF( not(WEEKDAY([Date],2)) in {6,7}, IF( LOOKUPVALUE('Holiday Table'[Holiday Date],'Holiday Table'[holiday date],[Date])<>BLANK(), 0, 1 ), 0 ) RETURN a1 ) var f = ADDCOLUMNS(c,"rank",RANKX(FILTER(c,[holidaycondition]<>0),[Date],,ASC)) var e= MINX(FILTER(f,[rank]=a),[Date]) return e   Output without Using Table refrence

Read More
DAX Tips: Year-to-Date & Year-over-Year in Power BI
PowerBI
DAX Tips: Year-to-Date & Year-over-Year in Power BI
Pruthvi Gohil
Feb 29, 2024

Introduction to Power BI and Year-to-Date(YTD) & Year-over-Year(YoY) Calculations Power BI is a data visualization and business intelligence tool that allows users to connect to different data sources, transform data, and create insightful reports and dashboards. With Power BI, users can easily perform complex calculations such as YTD calculation, which provides a way to view data from the beginning of the year up to a given point in time. YoY growth is a change in a metric compared to the same period one year prior. There are several approaches to achieve YTD & YoY calculation using DAX in Power BI. Let's use one of the approach to accomplish that.   What is Year-to-Date(YTD)? Imagine you’re in February, looking back at all the data from the beginning of the year (January 1st) until today. That’s YTD. It’s like a running total of your performance throughout the current year.   How to Calculate Year-toDate(YTD)? Assume we have a calendar & sales table and having a column for sales amount. Now use DAX to develop a measure that computes the current year's YTD revenue. Previous Year-to-Date(PYTD): Now, rewind to the same day in the previous year. The data from January 1st of that year up to that day is PYTD. It’s your benchmark, a reference point to compare your current year’s progress.   How to Calculate Previous Year-to-Date(PYTD)? Using SAMEPERIODLASTYEAR function we can get the same date of previous year. Year-over-Year(YoY) Growth: This is where things get exciting! YoY is the change between your current YTD and the PYTD for the same day. It tells you how much you’ve grown (or shrunk) compared to the same period last year.   How to calculate YoY growth : Subtract PYTD(YTD Rev LY) from YTD Revenue(YTD Rev) :   The DAX functions I utilized to get these calculations : LASTDATE(Dates) : Returns last non blank date STARTOFYEAR(Dates) : Returns the start of year SAMEPERIODLASTYEAR(Dates) : Returns a set of dates in the current selection from the previous year CALCULATE (Expression,Filter,Filter, …) : Evaluates an expression in a context modified by filters. DATESBETWEEN(Dates,StartDate,EndDate) : Returns the dates between two given dates.   Conclusion : Calculating YTD and YOY growth in Power BI using DAX is a valuable technique for analyzing financial performance and identifying trends. Furthermore, it's important to note that this comprehensive approach leverages only pre-defined DAX functions. By understanding and practicing these versatile functions, you can unlock the ability to perform a wide range of complex calculations within Power BI, ultimately transforming your data into actionable insights.

Read More
magnusminds website loader