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.How to add an additional filter on this DAX
I have uploaded my PBIX file here. I am trying to add an additional filter based on State (like CO or NM). Bottom is my measure I would like to modify: Question = IF( HASONEVALUE( vw_NH_ProviderInfo_CO[Facility]), VAR CurrentTestValue = [Forecast C2/3 Wtd] VAR _processingdate = Values ( 'Date Bridge'[ProcessingDate] ) return CALCULATE( SELECTEDVALUE( Test_260303[HealthInspectionRating]), TOPN(1, FILTER( ALL( Test_260303 ), CurrentTestValue <= Test_260303[StarCutOff] && Test_260303[ProcessingDate] IN _processingdate ), Test_260303[MaxTotalWHSS], ASC ) ) ) Basically, current measure does not consider this coulumn: [Test_260303].[Provider State]. I would like to appy [Test_260303].[Provider State] so that end result would be shown as 4 for ART and 3 for CAL: ALL & ARB belong to CO state. ART & CAL belong to NM state. Somehow, it shows corretly for ALL and ARB on the "Combined" tab even with the wrong logic. Thank you!
Conditional Formatting on a Matrix & More
I've been stuck on a problem for my work for a while. I've made progress, but I can't seem to find a prooper solution to my conditional formatting issue. In Microsoft Power BI, I want to setup a matrix visual to show when the Doc Num of a Contract is New, Open, or Closed using Conditional Formatting. This will be over the course of different Verisons of Data pulls, and separated in different Unit groups. I want to ensure that I can adjust the DAX so that Closed only appears after a Doc Num disappears (e.g. not showing Closed in the same version it last appears) My current table has 3 columns: Doc Num, String Version, Decimal number Unit, String Lastly, I want to apply the conditional formatting to the matrix itself. This is because for each version, the Doc Num may change from New, to Open to Closed. Here's my current code: ContDocument Status = VAR _CurrentVersion = SELECTEDVALUE(DataPage_Tables_Append1[Version]) VAR _DocNum = SELECTEDVALUE(DataPage_Tables_Append1[DAC Doc Num]) VAR _CurrentUnit = SELECTEDVALUE(DataPage_Tables_Append1[Unit]) VAR _LastVersion = CALCULATE( MAX(DataPage_Tables_Append1[Version]), FILTER( ALL(DataPage_Tables_Append1), DataPage_Tables_Append1[DAC Doc Num] = _DocNum && DataPage_Tables_Append1[Unit] = _CurrentUnit ) ) VAR _FirstVersion = CALCULATE( MIN(DataPage_Tables_Append1[Version]), FILTER( ALL(DataPage_Tables_Append1), DataPage_Tables_Append1[DAC Doc Num] = _DocNum && DataPage_Tables_Append1[Unit] = _CurrentUnit ) ) RETURN SWITCH( TRUE(), _CurrentVersion = _FirstVersion, "New", _CurrentVersion > _FirstVersion && _CurrentVersion < _LastVersion, "Open", _CurrentVersion = _LastVersion, "Closed", BLANK() ) This is what the Output currently looks like: The problem is, that almost all Doc Nums are in Closed status at the last Version (unless the Doc Num is New during that Version), and that is not actually true. Some of those Doc Nums should be in Open status because it is not confirmed that the Doc Num disappeared. Another functionality that I want to add to my matrix, is to have a Doc Num be reopened in case there is an edit, and the Doc Num must be revisied. I do not know how to do this. Any guidance on this issue would be appreciated. Thank you in advance! Best, tkerr98
Visualisation (Slicer and Button Slicer)
Hi everyone, I hope you all have a nice day. I want to ask (pictures attached), I gave an example of me using visualisation of "Button Slicer (Top)" and "Slicer (Bottom)" in the picture, I have a table where I have Reporting Date column then I make custom column for the quarters and months. The "Year" in the slicer is from Reporting Date hieararchy (Year), but the quarter and month in the slicer, they both are custom column that I made from the Reporting Date column (so they are not the hieararchy of "Reporting Date"). My questions (I don't mind if you answer with "Slicer" visualisation or "Button Slicer" visualisation): In Picture 1, If I select Q1 and it turns grey, how do the visualisation slicer for months turns black (for Jan, Feb, Mar) without me selecting the Quarter slicer? In Picture 2, If I select any month (for example: Aug) and it turns black, how do the visualisation slicer for quarter turns grey (Q2) without me selecting the Quarter slicer? In Picture 3, If I select Year "2025" and it turns black, how do the visualisation slicer for quarter and months also turns black without me selecting Quarter and Month slicer? Please kindly help me answer my questions. Thank you in advance. God bless. 😊🙏
SUMX giving zero total for a column in Table visual
I am looking at a table visual where the line totals are correct but the Grand Total is coming back as zero despite using SUMX The measure is as follows PreviousMonthValue = VAR _currentOpp = SELECTEDVALUE(Opportunities_ME[OpportunityNumber]) VAR _currentDate = MAX(Opportunities_ME[IngestionDate_EOM]) VAR _prevMonthEnd = EOMONTH(_currentDate, -1) VAR _lastAvailableDate = CALCULATE( MAX(Opportunities_ME[IngestionDate_EOM]), FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] <= _prevMonthEnd ) ) VAR _ValueLM = CALCULATE( SUMX( FILTER( ALL(Opportunities_ME), Opportunities_ME[OpportunityNumber] = _currentOpp && Opportunities_ME[IngestionDate_EOM] = _lastAvailableDate ),Opportunities_ME[EstimatedValueBase]) )+0 RETURN _ValueLM Anyone know why?Shape Map Fill Colors - States With Multiple Regions
Hi, I need help with using a shape map to display colors like the map below. I have a table with all states and the respective sales regions, some having two or three regions (for example, California and Oregon). However, I get stuck trying to get it to fill a state with multiple colors for more than one region. Any idea how to do this without creating a custom map? Here's what my current map looks like on my report divided into regions:
Rank measure help
Hi all, I’m hoping someone can help, as endless hours of searching and battling with co-pilot has proved useless 😞 I have email performance data and I’m simply trying to create a measure that ranks the performance of emails - based on their 'open rate' - within a selected time period. My data / tables are as follows: I have 1 table with email performance metrics / a calendar table / and a table that assigns each date to a category such as “Last month”, “Last 6 months”, “Last 30 days” etc and this is what gets used a slicer. My aim: I’m trying to create a measure that will return the rank of each email, based on its open rate % (i.e. sum of opens / sum of delivered), for the emails that were sent based on the date filter that has been selected e.g. for all emails sent out in the “last 7 days”. The biggest open rate = rank 1. Side note: The same email may be sent multiple times, but the open rate % needs to include all opens related to that email (e.g. if "Email 1" is sent on 2 dates, it is the collective sum of these opens - if those dates fall within the selected time period. Any help, advice or pointers would be massively appreciated !!No active relationship measure - working in test but not in my main file
Hi, I'm having an issue in my report with measures where I am (intentionally) not activating a relationship between my Policies table and the date table, this is because I am looking for the number of policies that are active on the last date of each year. I believe these measures worked fine for me previously, however I have recently noticed that any measures where I am not activating a relationship between the fact table and the date table are now returning no results at all. I have tested this in a new file and it works as expected, I have provided details below of the measure and the test I have used which reflects the relevant parts of my report (albeit there are more tables and relationships in the actual report). My question is what would possibly cause a measure like this to stop working? Any direction or thoughts would be incredibly helpful and I'm happy to provide more info if required but I cannot provide my original report as it is confidential. I have pasted an image below of the result I get on the test vs what I get on my report. Relationships Date[Date] 1-Many Polices[Inception Date] (Inactive) Date[Date] 1-Many Polices[Expiry Date] (Inactive) Policies[SchemeID] Many-1 Scheme[SchemeID] (Active) Date Table 1/1/21 - 31/12/27 Scheme Table SchemeID Scheme 1 A 2 B Policies Table PolicyID Inception Date Expiry Date SchemeID 1 01 April 2024 31 March 2025 1 2 01 May 2024 30 April 2025 1 3 06 April 2024 22 May 2024 1 4 08 August 2022 31 March 2025 1 5 09 September 2025 02 December 2026 2 6 01 January 2021 01 January 2027 2 Measure Policies in Force (As at end of period) = VAR dateMax = MAX('Date'[Date]) //End of period RETURN CALCULATE( COUNTROWS('Policies'), FILTER('Policies', 'Policies'[Expiry Date] >= dateMax && 'Policies'[Inception Date] <= dateMax)) Result in Test File Result in Actual
