Create measure to calculate the difference in values between latest date and previous date
Hi everyone, I need some help. I've already tried different things, including asking LLM, but without any success, so I'm here to ask smarter people for help. I'm trying to create a measure that will give me the difference in a value for a specific entity between the latest date and the previous date. I have 2 tables. The first one contains the date and the entity name and the second one contains the value I want to measure. Both tables are related by a key column and the dates are not daily dates, but ad hoc dates. Something like this: The results I expect to see from the measure should look something like this: And I should be able to put the measure on a card and filter it by the entity name to display the related result. I have tried calculating the latest and previous date and then calculating the corresponding latest and previous value but when I ask to return the difference between latest and previous I never get those expected numbers... My guess is that I'm not using the right formulas to calculates the latest and previous values... Does anybody have an idea? Before anyone asks, I cannot change how the source tables look like as they are automatically generated by a third party software. Thanks in advance.
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.
Would you like to know a secret way to delete your DAX UDF code?
🧠 Imagine you've written a brilliant DAX UDF in model view. Finally you decide to add a comment, and suddenly the whole function disappears. 'Undo' is not working. 🚨This happened to me a few days ago. It was my fault for trying to add a comment with "//" above the function header. However, I hadn't expected this to delete the entire function. 💡As I discovered, the problem isn't the comment itself, but characters that aren't allowed in a function name. After(!) deleting the function Power BI displayed a warning : "Special characters are not allowed in a function name..." That put me on the right track. Step-by-step guide to follow: ➡️ Write a DAX UDF in the model view (don't spend to much time on it, as your work will get lost) ➡️ Go to the top of the file ➡️ Press Shift-Enter ➡️ Go back to the top ➡️ Type "// Comment" or any other character that is not allowed in a function name ➡️ Press Enter to save your function ➡️ Is your function still there? ➡️ If not, check wether undoing (Ctrl-Z) works ❓ Is this a bug or a hidden feature? If it is a feature what is it good for? To permanentely delete my work? 🤔 ✒️ I look forward to your comments!
Apply Conditional Formatting to a Column and am seeking assistance
Hello All, I would like to apply conditional formatting to a column and am seeking assistance. My goal is to enable users to select a color that will automatically be applied to a column header in Power BI, based on data sourced from Excel. For instance, if a user selects '1', the color should be green; for '2', blue; and for '3', red. Please be aware that the report is updated monthly. Therefore, as users change values in Excel each month, these changes should be dynamically reflected in Power BI. I'm facing a challenge with applying conditional formatting to a column instead of a row. If you have a more effective way to model the data, your suggestions would be greatly appreciated. My brain is a bit fatigued, just want to see column conditional formatted.😁 Can anyone provide a solution for this? I have included a screenshot below for better clarity.Currency FX Rates
Hi everyone, I have a pretty challenging DAX task (In my opinion, hopefully someone tells me it's easy!). I am currently putting together a balance sheet and I have the following CurrencyFX table: company starting_date month rate type number Company_1 01/04/2026 Apr-26 0.01401 Monthly 0 Company_1 01/03/2026 Mar-26 0.014146 Monthly 0 Company_1 28/02/2026 Feb-26 0.014184 Monthly 0 Company_1 31/01/2026 Jan-26 0.014286 Monthly 0 Company_2 01/04/2026 Apr-26 0.864304 Monthly 0 Company_2 01/03/2026 Mar-26 0.871916 Monthly 0 Company_2 28/02/2026 Feb-26 0.878272 Monthly 0 Company_2 31/01/2026 Jan-26 0.867002 Monthly 0 Company_3 01/04/2026 Apr-26 0.008072 Monthly 0 Company_3 01/03/2026 Mar-26 0.008126 Monthly 0 Company_3 28/02/2026 Feb-26 0.008169 Monthly 0 Company_3 31/01/2026 Jan-26 0.00793 Monthly 0 Company_4 01/04/2026 Apr-26 0.864304 Monthly 0 Company_4 01/03/2026 Mar-26 0.871916 Monthly 0 Company_4 28/02/2026 Feb-26 0.878272 Monthly 0 Company_4 31/01/2026 Jan-26 0.882768 Monthly 0 Company_2 01/01/2026 Jan-26 0.903222 Account 20101 Company_4 01/01/2026 Jan-26 0.837333 Account 20101 Company_3 01/01/2026 Jan-26 0.008748 Account 20101 Company_1 01/01/2026 Jan-26 0.014078 Account 20101 Company_2 01/01/2026 Jan-26 0.903222 Account 20102 Company_4 01/01/2026 Jan-26 0.837333 Account 20102 Company_3 01/01/2026 Jan-26 0.008748 Account 20102 Company_1 01/01/2026 Jan-26 0.014078 Account 20102 Company_2 01/01/2026 Jan-26 0.903222 Account 20103 Company_4 01/01/2026 Jan-26 0.837333 Account 20103 Company_3 01/01/2026 Jan-26 0.008748 Account 20103 Company_1 01/01/2026 Jan-26 0.014078 Account 20103 Company_2 01/01/2026 Jan-26 0.903222 Account 20201 Company_4 01/01/2026 Jan-26 0.837333 Account 20201 Company_3 01/01/2026 Jan-26 0.008748 Account 20201 Company_1 01/01/2026 Jan-26 0.014078 Account 20201 Company_2 01/01/2026 Jan-26 0.903222 Account 20202 Company_4 01/01/2026 Jan-26 0.837333 Account 20202 Company_3 01/01/2026 Jan-26 0.008748 Account 20202 Company_1 01/01/2026 Jan-26 0.014078 Account 20202 Company_2 01/01/2026 Jan-26 0.830597 Account 20301 Company_4 01/01/2026 Jan-26 0.872245 Account 20301 Company_3 01/01/2026 Jan-26 0.008247 Account 20301 Company_1 01/01/2026 Jan-26 0.014376 Account 20301 Company_2 01/01/2026 Jan-26 0.830597 Account 20302 Company_4 01/01/2026 Jan-26 0.872245 Account 20302 Company_3 01/01/2026 Jan-26 0.008247 Account 20302 Company_1 01/01/2026 Jan-26 0.014376 Account 20302 Company_2 01/01/2026 Jan-26 0.830597 Account 20303 Company_4 01/01/2026 Jan-26 0.872245 Account 20303 Company_3 01/01/2026 Jan-26 0.008247 Account 20303 Company_1 01/01/2026 Jan-26 0.014376 Account 20303 Company_5 01/01/2026 Jan-26 1 Fixed 0 Company_6 01/01/2026 Jan-26 1 Fixed 0 Company_7 01/01/2026 Jan-26 1 Fixed 0 Company_8 01/01/2026 Jan-26 1 Fixed 0 Company_9 01/01/2026 Jan-26 1 Fixed 0 Company_10 01/01/2026 Jan-26 1 Fixed 0 Company_11 01/01/2026 Jan-26 1 Fixed 0 Company_12 01/01/2026 Jan-26 1 Fixed 0 My Base measures are: Actual = SUM(GLEntry[Amount]) Actual Cumulative = VAR CurrentMonth = MAX('DimDate'[Date]) RETURN CALCULATE( [Actual], FILTER( ALL('DimDate'), 'DimDate'[Date] <= CurrentMonth ) ) I have the following requirements: If CurrencyFX[Type] = "Monthly", multiply Actual by the current months rate for that company. This is a balance sheet so I will be using a cumulative figure. In January multiply Januarys Actual by January rate. In February, multiply January + February Actual by Februarys rate, etc. If CurrencyFX[Type] = "Account", when GLEntry[GLAccountNo] = CurrencyFX[number] for the specified company, multiply Actual by that rate in every month. If CurrencyFX[Type] = "Fixed" always multiply by that rate (Intention is to give Actual without conversion) I have the following DAX measures that work only when a company is selected. I need to be able to select all companies to provide group numbers. I added all companys to the fx table and added a relationship to DimCompany to see if this would fix it but it didn't. FX Account = CALCULATE( MAX(CurrencyFX[rate]), TREATAS( VALUES(GLEntry[GLAccountNo-3]), CurrencyFX[number] ), TREATAS( VALUES(GLEntry[$Company]), CurrencyFX[company] ) ) FX Monthly = CALCULATE( MAX(CurrencyFX[rate]), TREATAS( VALUES(GLEntry[$Company]), CurrencyFX[company] ), TREATAS( VALUES(DimDate[MonthYear]), CurrencyFX[month] ) ) FX Fixed = CALCULATE( MAX(CurrencyFX[rate]), TREATAS( VALUES(GLEntry[$Company]), CurrencyFX[company] ), CurrencyFX[type] = "Fixed" ) FX Rate = COALESCE( [FX Account], [FX Monthly], [FX Fixed] ) Actual FX = [Actual] * [FX Rate] Actual Cumulative = VAR CurrentMonth = MAX('DimDate'[Date]) RETURN CALCULATE( [Actual FX], FILTER( ALL('DimDate'), 'DimDate'[Date] <= CurrentMonth ) ) I hope I have explained that well enough! Looking forward to your inputs. I can't post GLEntry data for obvious reasons. Thanks!
Previous Year Measure for Line Chart
Hello. I am trying to create a YOY cumulative active customer by month line chart. The line should show the number of customers who made at least one purchase. One for Selected Year and another for Selected Year - 1. And, the total is rolling, so the line should be going up over time. Because there is a year filter in play, I am having difficulties writing a measure that provides a PY Active Customer line for the line chart. If I have 2026 selected, previous years' active customers is reduced. So, I have to removefilter the Calendar. However, this causes the PY active customer line ignore the month on the X axis resulting in a flat total line. Please help me write a measure that accurately calculates PY active customers and still works in a line chart. Should look something like this: Sample file here. Thanks.
