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.
Average of Previous Fiscal Year
Hey Guys, So i am stuck here calculating average of previous fiscal year for a measure. So i have a column called "FY" which has values as "FY17","FY18".Also i have a measure which calculates average of 4 columns : Average Index=(Average (COl1)+Average(Col2)+Average(Col3)+Average(Col4))/4 Now i need to caluclated "Average Index" for previous Fiscal Year . Please help me out guys this thing is really getting up my nerves now.Show selected value from slicer when time intelligence slicer selected
Hi, I want to display selected value from the month and year slicer. However when time intelligence slicer(Calculation Group) selected other than 'Current Period' measure is not displaying anything because the time intelligence looking at multiple months when I select QTR, Year to Date etc., My measure: Test = "Reporting Period: " & SELECTEDVALUE('Date'[Month and Year]) I can't use the edit interactions as I need to show Period value as well in the measure. Any help is appreciated. ThanksDisplaying sum of revenue for the same quarter weeks
Hi, I have a an issue I can't figure out. Here is a sample of my data: I need to create a measure, which would look at the last closed 4 quarters, and sum up the total revenue for the previous 13 quarter weeks (I show only 2 here). So on 23 Q1 quarter week 5 I had 1 EUR, Same quarter week for 23 Q2 I had 2 EUR, so on both rows the total should be 3 EUR as an example. How do I do it? Appreciate all the advice!Dynamic % Difference Dax when filtering on non consecutive years
Hi there, I have a matrix in power BI, items in rows, years in columns and a YOY% difference measure. I can;t show actual data, so here is an example in excel of what I'm doing: Problem is I'm not always comparing to previous year. Sometimes we may want to compare 2019 Vs 2022. This works in excel: Excel calculates the difference in the filtered period. However when I do this in Power BI, when I filter it returns the previous year % difference not filtered period. In power BI I am using a matrix like this: Fruit YoY% (from quick measures) code is: Fruit YoY% = IF( ISFILTERED('Date Table'[Date]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __PREV_YEAR = CALCULATE( SUM('Fruit'[Value]), DATEADD('Date Table'[Date].[Date], -1, YEAR) ) RETURN DIVIDE(SUM(Fruit[Value]) - __PREV_YEAR, __PREV_YEAR) ) Which works for YoY, but when I filter, eg, 2020 Vs 2022, it still shows 2022 Vs 2021 % dif. When I use the below code, it works when two filters are on, but defaults to 2019 Vs 2022: Fruit Earlier = VAR earlieryear = calculate( min ('Date Table'[Year]), allselected ('Date Table')) return calculate ([Total Fruit Sum], all ('Date Table'), 'Date Table'[Year] = earlieryear) Fruit Later = VAR lateryear = calculate( max ('Date Table'[Year]), allselected ('Date Table')) return calculate ([Total Fruit Sum], all ('Date Table'), 'Date Table'[Year] = lateryear) fruit % dif = var lateryear = calculate (max('Date Table'[Year]), allselected ('Date Table')) var maxyear = max ('Date Table'[Year]) var earlier_ = [fruit Earlier] return if (lateryear = maxyear, divide ([fruit later]-earlier_ , earlier_)) Ideally, I'd need top level to be YoY%, (across all years if possible but 2022 Vs 2021 would be fine), then the slice the years to get the below: So ideally it would be: 1. YoY% difference (2022 Vs 2021, 2021 Vs 2020 etc) 2. Slicer two years (eg 2022 Vs 2020) 3. Filtered years % difference eg 2022 Bs 2020) 4. Remove slicers to return back to YoY% difference (2022 Vs 2021, 2021 Vs 2020 etc) Any help on what DAX I need to make this work would really help!
Custom Month to date
Hello all, Our fiscal months are not based on calendar months but ends on the Saturday of every month. Therefore the our fiscal month starts on different day every month. I have the dax formula to calculate the last saturday of the month but I am not sure how to implement it to use in my slicer Here's the measure that give me the last saturday of the month customEOM = VAR dateVal = [Date] RETURN if ( dateVal > DATE ( '2018, 12, 31'), if ( dateVal > if ( WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) = 7, EOMONTH ( dateVal, 0 ), EOMONTH ( dateVal, 0 ) - WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) ), if ( WEEKDAY ( EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ), 1 ) = 7, EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ), EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ) - WEEKDAY ( EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ), 1 ) ), if ( WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) = 7, EOMONTH ( dateVal, 0 ), EOMONTH ( dateVal, 0 ) - WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) ) ), EOMONTH ( dateVal, 0 ) )DATESMTD misbehaving if Sales in current month are zero
If Sales for Salesperson in current month = 0, the following DAX will then fetch the Sales Amount for the last month that it can see Sales (at least I think this is what is happening) Sales MTD = CALCULATE([Amount Measure], DATESMTD('NAV_Sales History MASTER'[Posting Date])) So in this example, Sales for Jan are £0 but it is returning Sales for December For such a simple piece of DAX I'm struggling firstly to understand why it would retrieve the incorrect figure, and then why it would start looking for something outwith the current month? Totals for Salespeople who have sales in the current month are displaying fine. Any ideas at all?
