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!
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
Show latest value based on date and other criteria (measure or new column)
Hi, I am trying to create a measure or column that will allow me to easily bring the latest value into my visuals. Here is some example data; the highlighted column is what I'm trying to create. The end goal is to show the most recent sale as a flat line across a graph, so I can compare historical sales to the most recent one visually, by product. Also, the date needs to be dynamic so it automatically updates the values when fresh data is loaded. Thanks!Reference 1st digit in column
Hello All I have a report that has the below ID data. The start of the ID determines which naming convention it is part of so Starts with 1 = General Starts with 2 = Clothing This goes right up to 9. Is it better to duplicate column leaving the first digit then refer to another table or is there any other better way? ID Name 1 General 11 General 112.000 General 2 Clothing 26 Clothing 264 Clothing 27 Clothing 3 Shoes 33 Shoes 334 Shoes Thanks in advance
