Dynamic P&L fiscal year time intelligence for actual vs forecast vs budget vs same period last year
Hi Community, I am trying to create a dynamic time intelligent profit and loss statement comparing actuals to forecast, budget, and the same period last year for each line item. I am using a fiscal year calendar which means the standard time intelligence measures will not work. The comparison amount (column) needs to be the variance between the two and not the amount. For example, net sales, the actual amount is 10,000 and the forecast amount is 12,000. The P&L matrix/table should show 10,000 for the actual amount and (2,000) for the forecast amount. Below is a quick picture of what I am trying to achieve: Line-Item Actual vs Forecast vs Budget vs SPLY Net Sales 10,000 (2,000) (1,000) (3,000) COGS 4,000 2,000 1,000 500 Additionally, I would like to see this on a WTD, MTD, QTD, and YTD basis in one matrix/table. Below is a screenshot of the final product I am trying to create along with the format strings: As you can see, we have four different format strings: whole numbers, whole number dollars, decimal, and percentages. My data model is pictured below: Below is a picture of what I currently have: I have created all the individual line-item measures for actuals, forecast, and budget using the measure branching technique. However, I know this is not the best way to do this because I have three measures for each line item. For example, I have a measure for actual net sales, forecast net sales, and budgeted net sales. As you can see, the forecast, budget, and SPLY measures are not variance amounts. The actual, forecast and budget measures use the SWITCH technique which prevents me from calculating the variance because of the four different format strings and a custom time intelligence function. Below is an example of my Actuals measure: Act HL = SWITCH( [Selected Account], 1, FORMAT([Volume], "#,#;(#,#);-"), 2, FORMAT([Net Sales], "$#,#;($#,#);-"), 3, FORMAT([COGS], "$#,#;($#,#);-"), 4, FORMAT([Gross Margin], "$#,#;($#,#);-"), 5, FORMAT([MAP/MDF], "$#,#;($#,#);-"), 6, FORMAT([SG&A], "$#,#;($#,#);-"), 7, FORMAT([Combined EBIT], "$#,#;($#,#);-"), 9, FORMAT([Net Sales Rate], "$#,0.00;($#,0.00);-"), 10, FORMAT([COGS Rate], "$#,0.00;($#,0.00);-"), 11, FORMAT([Gross Margin Rate], "$#,0.00;($#,0.00);-"), 12, FORMAT([MAP/MDF Rate], "$#,0.00;($#,0.00);-"), 13, FORMAT([SG&A Rate], "$#,0.00;($#,0.00);-"), 14, FORMAT([Combined EBIT Rate], "$#,0.00;($#,0.00);-"), 16, FORMAT([GM as % of Net Sales], "0.0%;-0.0%;-"), 17, FORMAT([Combined EBIT as % of Net Sales], "0.0%;-0.0%;-") ) This measure above is then placed into a time intelligence pattern using the SQLBI DAX Patterns for Week-related calculations. As previously mentioned, I am using a fiscal year calendar so the standard time intelligence measures will not work. For example, the Act WTD column in my matrix above is: Act WTD = IF ( [ShowValueForDates], VAR LastDayOfWeekAvailable = MAX ( 'Date'[Day of Week Number] ) VAR LastFiscalYearWeekAvailable = MAX ( 'Date'[Fiscal Year Week Number] ) VAR Result = CALCULATE ( [Act HL], ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] ), 'Date'[Day of Week Number] <= LastDayOfWeekAvailable, 'Date'[Fiscal Year Week Number] = LastFiscalYearWeekAvailable ) RETURN Result ) The matrix has 16 measures like you see above (actual + forecast + budget + SPLY = 4 x 4 = 16 measures) Obviously this not an efficent or sustainable way to create this. I have a feeling Calculation Groups might help solve the problem. Additionally, when slicers are placed on the page, the query is slow because of the number of measures and the size of the data tables. So community, how can I achieve the above? Thank you for taking the time to read through my question.Data Analysis Expressions - Customized Martix Table
This Power BI report display historical trends from 2018–2023 of the Ozone Season Heat Input (MMBtu) across U.S. states and fuel types. It highlight the developement of a customized matrix visual for displaying the Yearly record along with the CAGR %, shared %, and YOY % on a Martix table with a dynamic state slicer for environmental compliance and emissions analysis across states. This is a continuous project : 1st : Adjusting-Unadjusted-Emissions-with-AI-Solution 2nd : Updated view - 2022 Unadjusted NOx Emissions _M KPI_Selector_Value = VAR _id = SELECTEDVALUE ( KPI_MatrixAxis[ID] ) VAR _display = SELECTEDVALUE ( KPI_MatrixAxis[Display] ) VAR _year = IF ( _id = 1, VALUE ( _display ) ) VAR YearlyTotal = CALCULATE ( [Total HTIOZ], REMOVEFILTERS ( 'Unadjusted Annual Unit'[Data Year] ), KEEPFILTERS ( 'Unadjusted Annual Unit'[Data Year] = _year ) ) VAR YoY = [_YOY % HTIOZD Data] VAR CAGR = [3Y CAGR HTIOZ] VAR Share_of_Total = DIVIDE ( [Total HTIOZ], CALCULATE ( [Total HTIOZ], ALLSELECTED ( 'Unadjusted Annual Unit' ) ) ) RETURN SWITCH ( TRUE(), _id = 1, FORMAT(YearlyTotal,"#,##"), _id = 2, YoY , --FORMAT(YoY,"0.0%"), _id = 3, CAGR, -- FORMAT(CAGR,"0.0%"), _id = 4, FORMAT(Share_of_Total,"0.0%"), BLANK() ) eyJrIjoiYTMzYWI3NWYtM2RjZC00MzRhLWIzNmUtYWQ0MWY2Mjc4MWEzIiwidCI6IjNlMjFhMTFlLTc3MDctNDdmOC1iMzRhLTc5YTQ2YTQ0ZTk5MyIsImMiOjF9Date table with Calendar and Fiscal year
DateFiscal = ADDCOLUMNS ( CALENDAR ( MIN ( 'Table'[Date column] ), MAX ( 'Table'[Date column] ) ), "MonthNo", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "MMMM" ), "MonthYear", FORMAT ( [Date], "MMMM YYYY" ), "MonthYearShort", FORMAT ( [Date], "MMM YY" ), "MonthYearNo", FORMAT ( [Date], "YYYYMM" ), "Quarter", QUARTER ( [Date] ), "Year", YEAR ( [Date] ), "QuarterYear", "Q" & FORMAT ( QUARTER ( [Date] ), "0" ) & " " & FORMAT ( YEAR ( [Date] ), "0000" ), "YearQuarterNo", FORMAT ( YEAR ( [Date] ), "0000" ) & FORMAT ( QUARTER ( [Date] ), "00" ), "Day", DAY ( [Date] ), "WeekNumber", WEEKNUM ( [Date] ), "WeekdayNum", WEEKDAY ( [Date] ), "WeekdayName", FORMAT ( [Date], "DDDD" ), "PreviousWeek", WEEKNUM ( [Date] ) -1 , "WeekStartDate", ([Date] - WEEKDAY ( [Date] , 1 ) +1), "WeekEndDate", ([Date] - WEEKDAY ( [Date] , 1 ) +7), "YearMonth", FORMAT ( [Date], "YYYY-M" ), "FiscalQuarter", SWITCH ( TRUE(), MONTH ( [Date] ) IN {8, 9, 10}, "Q1", MONTH ( [Date] ) IN {11, 12, 1}, "Q2", MONTH ( [Date] ) IN {2, 3, 4}, "Q3", MONTH ( [Date] ) IN {5, 6, 7}, "Q4" ), "FiscalYear", YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 8, 1, 0 ), 8, 1 ) ), "FiscalQuarterNo", FORMAT ( YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 8, 1, 0 ), 8, 1 ) ), "0000" ) & SWITCH ( TRUE(), MONTH ( [Date] ) IN {8, 9, 10}, "01", MONTH ( [Date] ) IN {11, 12, 1}, "02", MONTH ( [Date] ) IN {2, 3, 4}, "03", MONTH ( [Date] ) IN {5, 6, 7}, "04" ), "FiscalQuarterYear", SWITCH ( TRUE(), MONTH ( [Date] ) IN {8, 9, 10}, "Q1", MONTH ( [Date] ) IN {11, 12, 1}, "Q2", MONTH ( [Date] ) IN {2, 3, 4}, "Q3", MONTH ( [Date] ) IN {5, 6, 7}, "Q4" ) & " " & FORMAT ( YEAR ( DATE ( YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 8, 1, 0 ), 8, 1 ) ), "0000" ) )
Days of Supply
Suppose you have a weekly forecast of inventory and demand and you wish to know for each week the number of days of supply that you have on hand. That is the purpose of this Quick Measure. Inputs are the current week and inventory as well as the demand column. Days of Supply = // Get the current week and inventory for the current row VAR __week = MAX([Week]) VAR __inventory = MAX([Ending on hand Inventory]) // Create a table of all weeks greater than the current week VAR __table = FILTER(ALL(Inventory),[Week]>__week) // Add our current inventory from above to each row VAR __table1 = ADDCOLUMNS(__table,"__start",__inventory) // Add a running total of demand to each row VAR __table2 = ADDCOLUMNS(__table1,"__demand",SUMX(FILTER(__table1,[Week]<=EARLIER([Week])),[Demand])) // Add the difference in start versus the running total of demand to each row VAR __table3 = ADDCOLUMNS(__table2,"__left",[__start] - [__demand]) // Create a table that only has the positive rows VAR __table4 = FILTER(__table3,[__left]>=0) // With only the positive rows, the MIN is the last row before demand runs out VAR __min = MINX(__table4,[__left]) // Therefore, our base days is the number of rows in this table * 7 VAR __baseDays = COUNTROWS(__table4)*7 // Grab the MAX value of the negative rows, this is the row right after our inventory runs out VAR __max = MAXX(FILTER(__table3,[__left]<0),[__left]) // Divide the row right before the invetory ran out by the sum of the absolute values of right before and after // the inventory ran out. This is the percentage of days in that week before inventory ran out. multiply this by 7 // and this is the number of days in that week before inventory ran out VAR __extraDays = __min / (__min + ABS(__max)) * 7 RETURN __baseDays + __extraDays Interestingly, this Quick Measure exhibits a form of "looping" in DAX, or at least a work-a-round. Consider that a primary task of this measure is to determin the week in which inventory "runs out". In traditional programming, one would determine this with something like a for or while loop, checking for a boundary condition of the inventory on hand becoming negative with respect to demand. However, in DAX, there are no for or while looping constructs. Thus, instead we create a temporary table where each row in the table represents one pass or iteration through a traditional programming "loop". We can then use our boundary condition to filter down to the specific rows where that boundary condition occurs in order to perform our calculation. As demonstrated in the DAX code above, we can determine the values on either side of our boundary condition as well as how many "interations" were required in order to hit that boundary condition. eyJrIjoiZDcxY2U3ZjAtM2ZiMy00ZjJhLWE0N2YtZTM5YjFiNDJlMTJlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9Periodic Billing
Again, thanks to @Phil_Seamark's insightful guidance and examples in his fantastic new book, Beginning DAX with Power BI: The SQL Pro’s Guide to Better Business Intelligence, I finally "get" the GENERATE function and how it can be used to elegantly solve problems that have vexed me since almost the very first Power BI model that I ever built, dealing with data that contains date ranges. This one uses the same technique as Open Tickets but puts a different spin on it by also requiring that there be a periodic element to the totals calculation. The following measure assumes a disconnected date table and data that involves billing starting and ending dates with a monthly fee. The measure computes the total revenue within any particular month, which can then be plotted. Also nifty. Total Amount = VAR tmpCalendar = ADDCOLUMNS('Calendar',"Month",MONTH([Date]),"Year",YEAR([Date]),"MonthYear",VALUE(YEAR([Date]) & FORMAT(MONTH([Date]),"0#"))) VAR tmpBilling = ADDCOLUMNS('Billing',"MonthYearBegin",VALUE(YEAR([BeginDate]) & FORMAT(MONTH([BeginDate]),"0#")), "MonthYearEnd",VALUE(YEAR([UntilDate]) & FORMAT(MONTH([UntilDate]),"0#"))) VAR tmpTable = SELECTCOLUMNS( FILTER( GENERATE( tmpBilling, SUMMARIZE(tmpCalendar,[Year],[Month],[MonthYear]) ), [MonthYear] >= [MonthYearBegin] && [MonthYear] <= [MonthYearEnd] ), "Customer",[Customer], "Year",[Year], "Month",[Month], "Amount",[Amount] ) RETURN SUMX(tmpTable,[Amount]) Again, if you are only going to own one DAX book, IMHO, Phil's is the book you want! eyJrIjoiN2IyMGNlYmItZjhjNi00M2IxLWI1MDAtZmVkMzIxMjkzNmFhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Chelsie Eiden's Duration
Chelsie Eiden is my new favorite human being on the face of the planet. I don't know her major but, even if she is majoring in math, it still wouldn't change my mind on this one. That's how much I like this individual. The reason she is my favorite human being on the face of the planet is because she has finally...FINALLY, solved a "problem" with Power BI that is, ohhhh, say at least 4 or 5 years old. Since the dawn of Power BI there has been this problem with aggregating duration in HH : MM : SS format. You could convert it to seconds to aggregate it but you couldn't display it in the hours, minutes, seconds format in a visual that properly aggregated it in column charts because the minute you did a concatenation or a format on it, "POOF" it became text. Maddening!! I have been harping on this issue for, well, forever, such as in this post I did with konstantinos ages ago. So, Chelsie, thank-you, thank-you, thank-you from the bottom of my heart! I have named this new Quick Measure just for you. Chelsie Eiden's Duration = // Duration formatting // * @konstatinos 1/25/2016 // * Given a number of seconds, returns a format of "hh:mm:ss" // // We start with a duration in number of seconds VAR Duration = SUM([Duration]) // There are 3,600 seconds in an hour VAR Hours = INT ( Duration / 3600) // There are 60 seconds in a minute VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) // Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours VAR Seconds = ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number RETURN // We put the hours, minutes and seconds into the proper "place" Hours * 10000 + Minutes * 100 + Seconds All but the last line is the code from that article that konstantinos and I wrote years and years ago. The only difference is the last line. Once you have this measure, then all you have to do is implement Chelsie Eiden's Custom Format String with a value of "00:00:00" (no double quotes). Boom!! https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-2019-feature-summary/#customFormatStrings eyJrIjoiYjE5ZDZkN2EtODdlNy00ZmUxLWIyOGItOWRhYjU0NDY2Y2VhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
