DAX logic needed to determine order lines value of new, existing or shipped orders
Hello! I am trying to come up with the DAX logic to determine order lines value of new, existing or shipped orders. New order value needs to show a sum of USD of new order lines in the most recent week (orders not present in previous week). Existing order value needs to show a sum of USD of order lines that exist in both current and previous weeks (summing only values in current week to avoid duplication). Shipped value is sum of orders that exist in previous week, but not current week. I can’t split table into weeks, no new tables in DAX based on my fact table. I must only operate in DAX, no MS Query solutions. I am not restricted on the number of columns or measures I can create with DAX. I have ‘Calendar’ table in addition to my fact ‘Orders’ table. Here is sample of fact ‘Orders’ table:
Very high total table results, does not average.
HELP PLEASE! Hello, I have a problem that I have been looking for the solution for several days but I can't find it. I would be very grateful if someone could help me, because it is something that I will have to use a lot and I have no way to solve it. I have a table with data, in which when I make the comparison to see the % that we have gone up or down with respect to last year gives me very high results. When I do the comparison by date I get the total result very high, but when I do it by week and month I get all the results very high, both the total and the partial results. What I want is that the total result (like the weekly and monthly ones) gives me the average of all the results that compose it, and it is impossible that these very high numbers that I get are averages. I leave here below the images in which you can see my case. I am also going to leave the formula that I am using for it, since I believe that the solution may be that the dax formula that I am using is wrong. The formula is the following: R_LYDay(B) = VAR _actual_total = SUMX( VALUES(calendar_table[Date]), SUM(R) ) VAR _LY_total = SUMX( VALUES(calendar_table[Date]), CALCULATE( SUM(R), DATEADD(calendar_table[Date], -364, DAY) ) ) RETURN DIVIDE(_actual_total - _LY_total, _LY_total, 0) When I put “R” is where the main measurement is, but for confidentiality I prefer not to share. I would be very grateful if someone could give me a solution. Thank you very much in advance.Sorting Matrix Data Based on Slicer Selection
Hi Everyone, I want to sort the data in a matrix in a specific order. When I select a value in the slicer. Below is the raw data: The matrix visual contains Product and Value [Measure]. Raw Data Slicer The order in which the products need to be sorted depends on the year value selected in the slicer. Raw Data : Table Product Year Value P3 2023 100 P2 2023 200 P1 2023 300 P8 2023 40 P6 2023 100 P1 2024 200 P3 2024 300 P2 2024 40 P14 2024 100 P12 2024 200 slicer : Year 2023 2024 Required order, when we click on slicer : Order Required 2023 2024 Order order P1 P1 P2 P2 P3 P3 P6 P12 P8 P14
DAX calculate Previous month lost customer revenue
Hi guys, I am having trouble calculating the previous month lost customer revenue. My model relies on a single way relationship between thr fact table and the time table, so that is not possible to change. I would like to display the measure "Churn_lost_$" as shown in the picture below. Thank you very much! 🤙 You can donload the model here: https://drive.google.com/file/d/1MKFmtBIm1Z-yJ0--FhVRuIrZqWVV166c/view?usp=sharingNot able get last year complete number for current financial year
Hi Every I am trying to get the previous Year running total for the next fiscal year. However I am not able to get it for the current fiscal year. The formula I used: =VAR _date= MAXX( SAMEPERIODLASTYEAR(DATESYTD( 'Calendar'[Date] )), 'Calendar'[Date] ) RETURN CALCULATE ( [Total_Employee Count ], FILTER ( ALL('Calendar'), 'Calendar'[Date] <= _date )data is getting Aggregated / Dax help
Hi Everyone, I am trying to create a Pivot table using Dax in Excel I have 4 columns in Table 1) Request ID [ text ] 2) Current status [ text ] 3) Cv Date [ date ] 4) Days_CV [ Today Date - cv date ] [ num ] Note: I have blank values in the CV Date. issue: I am trying to get the Avg days on days_CV, when I use the below Dax formulas.. in Pivot, with Dax 1: code I am not able to see the JOB IDs, where the CV date is blank, but I want to see all the JOB ids if CV date is blank, and I want the formula to return blank In Dax code 2, I am getting an error dax1: =if( isblank([cv_date]), "",AVERAGE(Append1[Req - CV share] )) dax 2 : =CALCULATE( AVERAGE(Append1[Req - CV share]), KEEPFILTERS( FILTER( Append1, Append1[Requestion ID] ) ) ) bwlow pic result with Dax 1 below pic error, when i used dax 2
Need DAX to calculate total based on prior computed total per category
Hi - I have below data in Table View and need a Dax formula to compute a column with the total based on prior week's computed number (prior total * factor) for each category: Category Date factor Total calculation A 01/01/24 - 100 A 01/08/24 1.01682 101.68 (100*1.01682) A 01/15/24 1.01734 103.45 (101.68*1.01734) A 01/22/24 1.01939 105.45 (103.45*1.01939) A 01/29/24 1.01854 107.41 (105.45*1.01854) B 01/01/24 1.01792 50 B 01/08/24 1.01612 50.81 B 01/15/24 1.01332 51.48 B 01/22/24 1.01134 52.07 B 01/29/24 1.01166 52.67
I can’t use measure as a chart legend to categorize the data
Hello! I have a dataset like this: Date Client Total Sales Sales Prev Diff Sales MRR Measure 2024/January A 200 200 New 2024/February A 500 -200 Lost 2023/January B 100 New 2024/January B 650 150 Reactivation 2024/February B 150 -150 Lost 2023/December E 11885 New 2024/January E 6265 11885 -10670 Reduction 2024/February E 1515 -1215 Lost 2024/March E 6270 6970 Reactivation 2024/April E 64850 6990 7880 Expansion 2024/May E 3650 14850 -11700 Reduction I have written a DAX code as a measure. The code is as follows: MRR Measure = if([firstD]=1,"New", if(and([Total Sales]>=0,[Sales Prev]=0),"Reactivation", if([Diff Sales]>=0,"Expansion", if(and([Total Sales]=0,[Sales Prev]<>0),"Lost", if([Diff Sales]<0 , "Reduction"))))) and the code for the Different Sales: Diff Sales = [total sales]-[sales prev] I want to add a bar chart based on this different sales value and the date. It gives me something like this: I want something like below, but because I have written a measure I can’t move it to the legend field in the chart. What should I do?
