Dax to obtain Last Date, Result of Last Date, Second to Last Date, Result of Second to Last Date
Hi all, Hoping someone much smarter than me can help! Basically I have a table which is constantly being updated with more entries. There are many duplicate references which will have many resuls and visit dates. A snapshot of the data is below: As part of my analysis I want to compare the last 2 visits only (I want this to be dynamic so in a months time the data will change). I would like a table showing the columns like in the image below. Last Visit Date = The date the reference was visited last. Last Result = The Result of the Last Visit Second to Last Visit Date = The visit date before the last visit date Second to Last = The Result of the Second to Last Visit. No. of Days Difference = The number of days between the last and second to last visit. I managed to get a version of the Dates columns but I don't think it was ideal. I think the method I used is what's stopping me from getting the result which I also need. Anyone have any suggestions please? Thanks
calculate value on Lastdate if its blank
Hi all, I am searching for a dax formula that can provides a value from last available date. The Value for the date august and july is missing (only Company 2) and when I use this formula with visual filter on KPI it works: Measure Last date cogs = CALCULATE( Sum(Tabelle1[Value]), LASTDATE(Tabelle1[Date])) But I need a formula that can filter. I tried the following formula: Measure with filter = CALCULATE(SUM('Tabelle1'[Value]), FILTER('Tabelle1','Tabelle1'[KPI]="cogs"), LASTDATE('Tabelle1'[Date]) ) The last date is august 24. But cogs have no available value in august and july but I want the last date that is available (june). The problem is (I guess), Lastdate funktion identify the last date in the column date and the result is august 24 but for cogs is no data available.
Total not visible in Table
Hi everyone, I have a issue, where values are shown on row level but not at the total level. Logic for the calculation has to be as followed: 1. We have to check Billings for the previous month when the Net Billings was not 0, then I have to sum Cost after that period when Net Billings is not 0. 2. If there no record where the Net Billings is not 0 then sum all the Cost. Cost = CALCULATE( SUM('Project Actuals'[Internal_Costs]), USERELATIONSHIP('Project Actuals'[Date],'Rev Rec'[Date]))<p> </p><p> </p><p> </p><p> <li-code lang="markup">Billings = SUM('Rev Rec'[Net Billings]) Below is the DAX which I have tried, but it's not giving me total. Final Cost = VAR _currentmonth = SELECTEDVALUE('Rev Rec'[Date]) VAR _lastNonZeroDate = CALCULATE( LASTDATE('Rev Rec'[Date]), FILTER( ALL('Rev Rec'[Date]), 'Rev Rec'[Date] < _currentmonth && [Net Billings] <> 0 ) ) VAR _earliestDate = CALCULATE( MIN('Rev Rec'[Date]), FILTER( ALL('Rev Rec'[Date]), [Net Billings] = 0 ) ) RETURN IF( ISBLANK(_lastNonZeroDate), CALCULATE( [Internal Cost], 'Rev Rec'[Date] >= _earliestDate && 'Rev Rec'[Date] < _currentmonth), IF( _lastNonZeroDate <> BLANK(), CALCULATE( [Internal Cost], FILTER(ALL('Rev Rec'[Date]), 'Rev Rec'[Date] > _lastNonZeroDate && 'Rev Rec'[Date] < _currentmonth))Below image shows, the first scenario when we have a Value in Net Billings, so in this case I need sum after that period. Second Scenario, when there is no values for previous months in Net Billings, then we have to sum everything in Cost. I would appreciate your help, and if there are any better solution for this. Thanks, Nik
Counting orders based on their latest status
Hello, For some time now I am strugling with measure that will count orders based on their last status. I tackled this from multiple angles and even ask ChatGPT for help, but couldn't find a solution. My goal is table visualization with columns for: names of statuses in first column (STATUS_DICTIONARY[NAME]), number of orders which have given status as their latest, % of GT My model looks like this: ORDERS[ID] - 1 to * - ORDERS_STATUS[ID_ORDER] STATUS_DICTIONARY[ID] - 1 to * - ORDERS_STATUS[ID_STATUS] And below is some sample data: ORDERS ID NUMBER 1 23/01/1 2 23/01/2 3 23/01/3 4 23/01/4 5 23/01/5 ORDERS_STATUS ID ID_ORDER ID_STATUS DATE_CHANGE 1 1 1 26.09.2023 2 1 2 26.09.2023 3 2 1 26.09.2023 4 3 1 27.09.2023 5 4 1 27.09.2023 6 1 3 27.09.2023 7 2 5 28.09.2023 8 3 2 28.09.2023 9 3 3 29.09.2023 10 5 1 30.09.2023 11 4 2 30.09.2023 STATUS_DICTIONARY ID NAME 1 aaa 2 bbb 3 ccc 4 ddd 5 eee Based on all above I expect result to look like this: STATUS NAME NUMBER OF ORDERS % OF GT aaa 1 20% bbb 1 20% ccc 2 40% ddd 0 0% eee 1 20% I will be very gratefull for any help. Thank you!
Last values from measures in a table
Hi, I need to calculate last values from measures in a table. The table below shows last 2 dates from 1 year selection. The Grade and Freq measures and based on MEAN ans STDEV.S calculations I need measures to calculate Max Grade to equal a reslut of "B" Max Freq of the Max Grade to equal a reslut of "High" (if there was a high and low result for Grade "B" the Freq = "Low") I also need a measure to concatenate the Analyte names for every Grade = Max Grade for the entire selected date range 4/8/2020 has highest grade "C" so Concatenate = Cu, Se, Zn I can cant filter to last date as this would change the calculations for Grade and Freq as they are dynamically based on the entire selected date range. Last 2 dates First 2 dates Calulations: Cont Mean = CALCULATE(DIVIDE([Cont Total],[Cont Count])) Cont Total = [Historical Total]+[Samples Total] Cont Count = [Historical Count]+[Samples Count] Cont Std Dev = CALCULATE(STDEV.S('Labware'[Calculated Value]),DATESBETWEEN(Labware[Calendar Date],[Historical Date],[First Sample Date]),ALL(Labware[LIMS Text ID])) Grade = Switch( True(), 'Labware'[CtA]<3.5,"A", 'Labware'[CtB]<3.5,"B", 'Labware'[CtC]<3.5,"C", 'Labware'[CtD]<3.5,"D", "E") CtA = CALCULATE(IF([V]>0,[Cp]+(([V]-(MAX('Labware'[A Grade])-1.78*[Cont Std Dev]))/[Cont Std Dev]),IF([V]>MAX('Labware'[A Grade]),7,0))) CtB = CALCULATE(IF([V]>0,[Cp]+(([V]-(MAX('Labware'[B Grade])-1.78*[Cont Std Dev]))/[Cont Std Dev]),IF([V]>MAX('Labware'[B Grade]),7,0))) CtC = CALCULATE(IF([V]>0,[Cp]+(([V]-(MAX('Labware'[C Grade])-1.78*[Cont Std Dev]))/[Cont Std Dev]),IF([V]>MAX('Labware'[C Grade]),7,0))) CtD = CALCULATE(IF([V]>0,[Cp]+(([V]-(MAX('Labware'[D Grade])-1.78*[Cont Std Dev]))/[Cont Std Dev]),IF([V]>MAX('Labware'[D Grade]),7,0))) Thanks RodneyDAX - measurements in past periods
I need to automate the measures below to show according to the column period: Receita Liquida NET = VAR varOrderContexto = MAX('Esqueleto DRE'[Ordem]) Return CALCULATE( [Realizado], FILTER( ALL('Esqueleto DRE'), 'Esqueleto DRE'[Ordem]<=varOrderContexto ), NOT( ISBLANK('Esqueleto DRE'[Ordem]) ), FILTER( ALL('Esqueleto DRE'), 'Esqueleto DRE'[Ordem]=1 ))
Evolution of value per id filtering by date
Hi, I have been investigating on the forum but still have some issues. I have a table like this: Id Date Value 1 1/1/2020 Prospect 1 2/2/2020 Lead 1 5/5/2020 Opportunity 2 3/3/2020 Prospect 2 4/4/2020 Lead 2 7/7/2020 Opportunity I need a measure that shows the last value by id filtering by month. For example, if I select march/2020 I need to have something like: Id value 1 Lead 2 Prospect Then, I need to display a chart that shows the number of distinct id´s per value filtering by month If I select 5/5/2020, I need to see something like: Value Count of id Prospect 0 Lead 1 Opportunity 1 This means, we count the number of id´s per value, depending on the last value of an id at a certain date
CALCULATION THAT RETURNS THE LAST MONTH ACCORDING TO THE CATEGORY
HI, I wonder if anyone can help me in the following situation I have a category of documents and each document has status (A or B) I need to do a calculation that every last month shows all categories in a table and in the other months show only category A EX: HOW IS IT as it should show in the table in power bi As April is the last month only he brings all categories A and B
