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.Conditional Formatting with Calculation Groups - Issues, please help me
Hi community, I developed a model with some conditional formatting, and I'm quite happy with it. Since the number of measures is quickly growing, I've started to implement Calculation Groups, to make it more efficient, and the model actually works fine with calculation groups implemented. Despite that, I found some issues with a simple conditional formatting configuration. Download - Sample Model (300 kb): https://www.dropbox.com/scl/fi/4sju5841r4m3afq7tewdo/Help_CalcGroups.zip?rlkey=v65nx5eshluxkz310ta7jacxe&dl=0 My basic conditional formatted visual without calculation groups looks as follows, and works just fine. The formatting condition is pretty simple and based on one field. The background of the rows of the table 'PL_Structure' is light blue where the field 'PL_Structure' [PL_Detail] has value 0 (running total rows), while are colored in light grey where the field value is 2 (subtotal row). The other - common rows - stay white (default color). The structure of the table can be found in the attachment together with the model (DataSource.xlsx), and includes an explanation of key fields as well. My formatting conditions The "PL SubTot Color" measure simply reads the SELECTEDVALUE of the above mentioned [PL_Detail] field): Now, with calculation groups implemented, I'm experimenting a strange behaviour, the numbers are just ok, but the table looks as follows: Maybe I did not fully understand the logics behind calculation groups, but I find them extremely useful for maintenance purposes. The attached model includes a side by side (with / without calculation groups). I do appreciate any suggestion or fix. Thank you. MarcoHow do I detect the active calculation item of a calculation group for a measure?
Suppose in this example I have a calculation group for cumulative sum I would like to have a measure that can detect what the active calculation item is. I notice that the Calculation Group can be accessed like a table in DAX. I create a measure to concatenate the measure names: Detect CalcGroup = CONCATENATEX('CalcGroup Cumulative Sum', CONVERT( [Cumulative Sum Group], STRING), ",") The card measure shows that it is working when both are not filtered out. However, when only one is filtered, an error is shown: I do not know why this is happening. As you can see in the DAX measure, I have purposely converted the [Cumulative Sum Group] column into a String just to see if this fixes the problem, but it does not. Also, it works fine for multiple values, but not for a single value. I was hoping to use this mechanism to detect which calculation group is active, is there any possibility of this working?
Calculation groups possibility to improve performance
Hi Champs, My dashboard report page has two overview boxes (one for the selected year and another for prior year) for different category values (such as Sales, Revenue, Profit, and Margin). Right now, we are using two measures for calculating each of the category values as mentioned below: Sales = sum(Data[sales]) Revenue = sum(Data[revenue]) Profit = sum(Data[profit]) Selected year = Year[Year] //Disconnected table to "Data" table Selected year sales = Calculate(Sales,Data[Year] = Selected year) Prior year sales = Calculate(Sales,Data[Year] = Selected year - 1) Selected year revenue = Calculate(Revenue,Data[Year] = Selected year) Prior year revenue = Calculate(Revenue,Data[Year] = Selected year - 1) Selected year Profit = Calculate(Profit,Data[Year] = Selected year) Prior year Profit = Calculate(Profit,Data[Year] = Selected year - 1) Now, the request is to reduce the number of measures. As the number of categories are increasing, would using calculation groups be a better option to improve performance? If yes, please can someone point in the direction to implement the calculation group. Thanks, AnthonyJosephCalculation Groups Very Slow
I am working on a report that uses calculation groups to let users select which time intelligence groups they would like to see (CY YTD, PY PTD, YTD Variance, CY TTM, PY TTM, TTM Variance). There is also a perspective set up to allow them to pull in certain dimensions from the dataset. This is all set up in Tabular Editor opened from the dataset. The KPIs are DAX measures within the Power BI Report itself. The dataset is a star schema and has several million records. Does anyone have any advice for how to optimize and speed up calculation groups within Power BI? Could it be related to my DAX measures being in the report instead of the DS? Any advice or things to try would be helpful. Thanks!Subtracting two measures not working
Hi Community, I am having an issue with what should be a simple thing to do, calculating the difference between measures. Below is the table: The "Account" field in rows is a calculated item from Calculation Group 1 and the "Time Calculation" is a calculated item from Calculation Group 2. As you can see, the difference between the whole number values is calculating correctly but the difference between the decimal numbers and percentages is not calculating correctly. (Highlighted in yellow) The three variances are calculated as follows: vs QBR = [Act] - [QBR] vs AOP = [Act] - [AOP] vs LY = [Act] - LY] The decimal numbers are taking "Account" divided "Volume". For example, Net Sales in Calculation Group 1 (row 10 in the matrix) Net Sales = VAR NS = CALCULATE ( SELECTEDMEASURE (), 'ACT/QBR/AOP'[Line Item] = "Net Sales" ) VAR Volume = CALCULATE ( SELECTEDMEASURE (), 'ACT/QBR/AOP'[Line Item] = "Volume" ) RETURN DIVIDE ( NS, Volume, 0 ) All other decimal numbers are calculated the same way except for the first variable. I am not sure what the issue is but hoping someone can assist with what should be an easy thing to do. Thank you!
Calculation Groups: time intelligence for actual vs forecast vs budget
Hi Community, What I am looking to create is a table/matrix that compares a full P&L for actuals, forecast, budget, and last year's results all of which can be seen on a WTD, MTD, QTD, and YTD basis. Below is a screenshot of what I am looking to do: I have all the base and time intelligence written using the measure branching technique. This has been very inefficient because I have a lot of repetitive measures that change a few variables along with the SWITCH measure to create the visual P&L. I believe calculation group(s) would be the most effective strategy to achieve this result. What is the best approach for this solution? Are multiple calculation groups required? Thank you!Sum calculation group items
I've got a calculation group for general ledger accounts, and I'd like to use that calculation group in a visual to sum two of the accounts. However when I add the calculation group to that visual, of course I get all accounts. I need to filter. So I'm trying to use the filter panel to select only the two relevant accounts, but the sum is this not filtered for this. That sort of makes sense to me because I'm asking PowerBI to implicitely calculate, which isnt supported with Calc groups. But, any aidea how to solve this challenge? The short-cut will probably be to create a separate calculation item, but I'd rather limit the number of items
