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 use timestamp in moving average calculation
Hi! I'm trying to do a moving average based on the last N rows but nothing I've tried seems to work. When I do the average calculation I'm trying to use the current row minus N rows, but the code does not consider the time-part of the timestamp, only the day (in this case the 16th). How can I get the full timestamp into the calculation in order to get the moving average to work? The DAX: Wagon Ore Normal Load AVG MA7 timestamp = IF ( NOT ISBLANK ( [Wagon Ore Normal Load AVG] ), CALCULATE ( [Wagon Ore Normal Load AVG], FILTER ( ALLSELECTED ( 'F WAGON CYCLE' ), 'F WAGON CYCLE'[TRAIN_CYCLE_TIMESTAMP] <= MAX ( 'F WAGON CYCLE'[TRAIN_CYCLE_TIMESTAMP] ) && 'F WAGON CYCLE'[TRAIN_CYCLE_TIMESTAMP] >= MAX ( 'F WAGON CYCLE'[TRAIN_CYCLE_TIMESTAMP] ) -2 ) //The minus N makes no difference ) ) As you can see in the image below, the MAX considers the row on a day level.Issues Creating a 3 Column IF / Else in DAX Measure to Display the First Column that Has Data In It
I have a Power BI Report that contains many rows and each row has the same 3 address columns, displayed in the order below from left to right. Home Address: Street Mailing Address: Street Business Address: Street In each row, one or more of those columns may be populated with data. Sometimes, the Home Address: Street is populated, but sometimes it's blank and the Mailing Address: Street is populated, in some cases both the Mailing Address: Street and Business Address: Street is populated, and agian sometimes only the Business Address Street is populated. I have a Dax Measure / Column named DAX Address Street and I need it to use the following logic: Look at Home Address: Street and if there is data present in that colum use that, and stop looking. But, if Home Address: Street is blank, check the Mailing Address: Street column and if data is present there use that, and stop looking. But if Mailng Address: Street is blank, then Check the Business Address: Street and if data is present there use that, but if that's blank too, then display the following text in the DAX Address Street Field "Client Does Not Have a Street Address On File". Now, I wrote the following DAX Code, but even though it appears to be syntactically correct, it results in a gray screen with a memory error, even though I have 64 GB RAM on the machine that's running it. I am just getting into Power BI and DAX so I am certain I did something wrong. Hence, the reason I am here seeking guidance and assistance in determining what I need to do to achieve the objective listed above. The report columns, DAX Code, and Desired Result I am seeking to achieve are shown in the image below. RAW DAX Code is here in case it is needed: DAX Street Address = IF( ISBLANK(SELECTEDVALUE('Insured_Member_Dependents'[Home Address: Street])), IF( ISBLANK(SELECTEDVALUE('Insured_Member_Dependents'[Mailing Address: Street])), IF( ISBLANK(SELECTEDVALUE('Insured_Member_Dependents'[Business Address: Street])), "Customer Does Not Have Any Address On File", SELECTEDVALUE('Insured_Member_Dependents'[Business Address: Street] )), SELECTEDVALUE('Insured_Member_Dependents'[Mailing Address: Street] )), SELECTEDVALUE('Insured_Member_Dependents'[Home Address: Street] )) How can I achieve the objectve laid out above given the data I provided herein? Any help, guidance, suggestions, or advise would be greatly appreciated.Count number of times something happens in measure
Hi all, I already had a ton of help from the community on my question, but I got stuck again (see this question for the earlier help: Solved: Count number of times something happens in measure - Microsoft Fabric Community) I would like to count the number of times per date per part that the percentage #good/#done is below 75% AND I would like to count the number of time per date per part that the percentage (#planned - #done)/#planned is above 10%, and do this in a measure. When either the #good/#done is below 75% or (#planned - #done)/#planned is above 10% I call this a black day. In another measure I would like to calculate the same, however I would like to count the number of times per date per part that the percentage #good/#done is below 85% AND I would like to count the number of time per date per part that the percentage (#planned - #done)/#planned is above 5%, and do this in a measure. When either the #good/#done is below 85% or (#planned - #done)/#planned is above 5% I call this a red day. However, when something is already categorized as a black day, it cannot be a red day anymore. E.g. #good/#done: - 1 jan 2024 for part X: (55+55)/(60+70) = 84,6% - 2 jan 2024 for part X: (55+55)/(55+100) = 71,0% - 3 jan 2024 for part X: (55+55)/(60+60) = 91,7% - 1 feb 2024 for part X: (62+60)/(60+70) = 93,8% E.g. (#planned - #done)/#planned: - 1 jan 2024 for part X: (60+70 - (60+70)) / (60+70) = 0% - 2 jan 2024 for part X: ((80+90) - (55+100)) / (80+90) = 8,8% - 3 jan 2024 for part X: ((60+75) - (60+60)) / (60+75) = 11,1% - 1 feb 2024 for part X: ((60+70) - (60+70)) / (60+70) = 0% Therefore the days will be qualified as follows: - 1 jan 2024 for part X: red OR no qualification, so red - 2 jan 2024 for part X: black or red, so black - 3 jan 2024 for part X: no qualification or black, so black - 1 feb 2024 for part X: no qualification or no qualification, so do not count Eventually I would like to count the number of times per month that this happens, and also be able to show exactly on which day this happened. For example for part X: Month Black day Red day Jan 2024 2 1 Feb 2024 2 0 March 2024 (just as example added) 5 1 And if I then want to show it per day for january for example (and then only show the rows with a red or black day): Day Black day Red day 1 jan 2024 1 2 jan 2024 1 3 jan 2024 1 2 feb 2024 1 3 feb 2024 1 What I did so far is: Create a measure to calculate #good/#done Percentage punct = VAR _currentDatePunc = MAX ( 'Table'[Date] ) VAR _currentPartPunc = MAX ( 'Table'[Part] ) VAR _SumGoodPunc = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDatePunc && 'Table'[Part] = _currentPartPunc ), 'Table'[#good] ) VAR _SumDonePunc = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDatePunc && 'Table'[Part] = _currentPartPunc ), 'Table'[#done] ) RETURN _SumGoodPunc / _SumDonePunc Create a measure to calculate (#planned - #done)/#planned Percentage Uitv = VAR _currentDateUitv = MAX ( 'Table'[Date] ) VAR _currentPartUitv = MAX ( 'Table'[Part] ) VAR _SumGeplandUitv = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDateUitv && 'Table'[Part] = _currentPartUitv ), 'Table'[#planned] ) VAR _SumDoneUitv = SUMX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDateUitv && 'Table'[Part] = _currentPartUitv ), 'Table'[#done] ) RETURN (_SumGeplandUitv - _SumDoneUitv) / _SumGeplandUitv Create a measure for #good/#done red days: MEASURE Punc RD = VAR _VirtualTbalePunc = SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[Part], "_percentage", 'Table'[Percentage punct] ) RETURN COUNTX ( FILTER ( _VirtualTbalePunc, 'Table'[Percentage punct] < 0.85 && 'Table'[Percentage punct] >= 0.75), [_percentage] ) Create a measure for (#planned - #done)/#planned red days: MEASURE Uitv RD = VAR _VirtualTbalePunc = SUMMARIZE ( 'Table', 'Table'[Date], 'Table'[Part], "_percentage", 'Table'[Percentage Uitv] ) RETURN COUNTX ( FILTER ( _VirtualTbalePunc, 'Table'[Percentage Uitv] > 0.05 && 'Table'[Percentage Uitv] <= 0.1), [_percentage] ) But then I am stuck, so hope anybody can help me further :). Thanks in advance.
Same period previous N months
I want to calculate total sales for previous N months for the same period according to the slicer. Eg if in slicer I select range from 1-10-21 to 3-10-21 the my column chart should display total sales for previous N months within this range. I wrote following Dax measure for the same but not getting desired result: same period = CALCULATE(SUM(financials[ Sales]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]), -'Select N'[N Value],MONTH),FILTER(financials,AND(financials[Date]>=MIN('Date'[Date]),financials[Date]<=MAX('Date'[Date])))) Any suggestions are apprecited.
Lookupvalue where measure = 1
Hi Everyone, Hitting my head against a wall on this DAX for a while now, and figured it was well past time to see if anyone has insights on what I'm trying to do. My goal is to create a raw data dive that reflects data as of a certain date for teams to sort through. The issue I'm having so far is that when I try to do that, if there is a change in value to the employee's data, it will duplicate lines of data where the only difference will be to reflect that change, rather than show the appropriate one. Data Structure The data table I'm working on is effective dated. The columns I care about are: 'Employee ID', 'Employee Key', 'Effective Date', 'End Effective Date' Employee ID is the ID number associated with Each Employee. Each Employee ID will have multiple records attached to it, each of which will have a beginning date and ending date (Effective Date and End Effective Date respectively) Employee Key is an index column where each row gets a distinct number to help identify it in the full table. Headcount Measure Dax is Below, but the goal of this measure is to show which records per employee ID are active within a time period (there are additional filters involving employee status that aren't relevent here but are built into the Headcount Measure Dax). This Dax works and I don't have issues with it but it's relevant to the question below. HEADCOUNT_EOP = VAR _date = MAX('Date'[Date]) RETURN CALCULATE( DISTINCTCOUNT('Timekeeper History'[Employee_ID]), _date >= 'Timekeeper History'[Map.PSJob.EffectiveDate] && _date < ('Timekeeper History'[Map.PSJob.EndEffectiveDate]), 'Timekeeper History'[RecordEndDate] >= 'Timekeeper History'[Map.PSJob.LastHireDate] ) Here's what I'm Trying To Do My headcount measure above flags records that are "live" when I move around in my timeline. So when I change a date filter, or map out a bar graph to reflect timeline, it will count the appropriate records accordingly. Is there a way to create a dynamic employee key that basically goes: IF HEADCOUNT_EOP = 1, [HERE IS THE EMPLOYEE KEY THAT IS ASSOCIATED WITH THAT LINE OF DATA], "" ? My goal here is then to be able to filter out rows of data where the dynamic employee key =/= that line of data's employee key. Everything I have tried has gotten mad at me with how many variable maximum values I've been playing with. Is this doable? Does this make sense?
Remove expand/collapse option (+/-) for rows which don't have subrows in a matrix
Hi all, I used a measure kindly suggested by another user on this forum to group measures in a matrix. Is it possible to remove +/- option for the rows which don't have subrows (in my example, hours, there is no need for this as it has only one value), but keep them for other rows. So ideally it should look like this Please find the attached Pbix file . Appreciate your help!How to calculate % of a count of a column?
I'm trying to calculate the % of waste distribution type for every month year so for example in the above image, I want a measure to calculate the % of every month so it should be like for Jan 2021 the % of Closed loop recycling =( 7/(7+15+24))*100 and same for landfill and open loop recycling. I want to display the % of waste distribution in a line chart against the date to get a trend of how much is he % of waste distrbution for every type in every month and year. How do I create a measure for this?
