Approximate Beg Backlog and Approximate ending Backlog DAX not working as required
Hi, I am new to power bi. I have a scenario in which I have to calculate Approximate Beg Backlog & Approximate Ending Backlog. The dax formulas for both are as follows: Approximate Beg Backlog = Approximate Ending Backlog(previous Week), Approximate Ending Backlog =Sum of Approximate Beg Backlog, Net Sales (Bookings) through Cancelations. As you can see, the formulas are interdependent and causing circular dependency with sample dataset as follows: 3-Oct 10-Oct 17-Oct Approximate Beg Backlog 0 $59,985,386 $548,181,595 54897953 A ($2,045,676) ($2,016,818) ($2,200,297) B ($2,435,328) ($2,400,974) ($2,619,401) C ($402,131) ($748,999) ($463,944) Approximate Ending Backlog $59,985,386 $54,818,595 $542,897,953 old beg New Beglog New Beglog New Beglog Approximate Ending Backlog= var date1=FIRSTDATE(MarketTable[Start MinWeek]) var addoffour_previouseweek = CALCULATE([addoffour],DATEADD(MarketTable[date],-7,DAY)) var addoffour_prepreviouseweek = CALCULATE([addoffour],DATEADD(MarketTable[date],-14,DAY)) var prepreweek = CALCULATE([addoffour],DATEADD(MarketTable[date],-21,DAY)) var pr1 = CALCULATE([addoffour],DATEADD(MarketTable[date],-28,DAY)) return IF(date1=MIN(MarketTable[date]),[addoffour],[addoffour]+addoffour_previouseweek+addoffour_prepreviouseweek+prepreweek+pr1) The above formula for Approximate Ending Backlog is giving the result but I have to put variables for each week. I am looking to get this loop functionality dynamically. How to get it using dax?
Calculating a dynamic 100% stacked visual denominator with multiple user defined variables
I am ultimately trying to come up with a measure that will allow me to plot a 100% stacked ribbon chart, and I'm getting hung up on trying to create the denominator for my measure Scenario: I have a year Slicer that allows multiple selections from the Table 'Year', column named [Year] I have a location slicer that allows muliple selections from the table 'Location', column named [Location Name] I have a table of data named 'Table1' that contains the following columns:[Year],[Location], [Type], & [Count] I want to calculate the percentage of a location type's count of that year's total. I also want the yearly total to adjust if the location slicer is used to filter out unwanted locations. Filtering by type is not allowed, only year and location filtering are allowed. An example of my dataset: Table1 Year Location Type Count 2020 A Red 5 2020 A Orange 6 2020 A Black 9 2020 B Orange 8 2020 B Black 5 2020 C Red 5 2020 C Orange 6 2020 C Black 33 2020 C Silver 2 2020 C Green 5 2020 D Green 4 2020 D Silver 14 2021 A Silver 6 2021 A Red 18 2021 B Orange 2 2021 B Black 15 2021 B Green 1 2021 C Silver 2 2021 D Silver 20 2021 D Green 11 2021 D Orange 15 2022 A Orange 4 2022 A Red 6 2022 A Black 9 2022 A Silver 8 2022 A Green 8 2022 B Red 9 2022 D Red 8 2022 D Silver 7 2022 D Green 15 Desired Outcomes: No Filtering: Filter on locations A & D Filter on Years 2020 & 2022 Filter on Years 2020 & 2022 & Locations A & D Any help would be appreciated. I've been trying a calculate function but can't seem to nail down the proper filters. I either end up a denominator equal to the numerator so every type has a value of 100% or I end up with the denominator equal to the yearly total with no location filtering, so the values don't add up to 100% when a location is selected, but looks correct when no filtering applied
Sales last year, same week number, same day
Hello there, I am currently having current year sales and have a measure calculating last year sales. However, it is looking on the same day last year increments but I am more interested in looking in the same week number, same day number (so if it is week 42, Monday, to look last year in week 42, Monday). I have managed to get the correct sales per day by creating a new collumn like this : DayWeekYear = ([UgedagNummer]*100+ [UgeNr])*10000+[År] and then a measure like this : Oms. kr. SÅ try = CALCULATE(SUM(F_Butikssalg[Oms]),FILTER(ALL(D_Periode),D_Periode[DayWeekYear] = MAX(D_Periode[DayWeekYear])-1)) BUT, this works only on a day level visual. If I want it displayed as a cummul of all values in a week or years and filter a longer period, it will not take all sales per specified period but rather print a value for one day. Oms is current sales. Oms. kr. SÅ is the current measure matching the same day last year (ex. 16 Oct with 16 Oct) and Oms. kr. SÅ try is last year sales taking into account week and day number( day number is : Monday=1; Tuesday=2.....Sunday=7) I want when selecting sales last year to have them dispayed looking on the same week number and day number on all levels of visualization and filtering: day/week/month/year. I have tried making the same column only with year&week but then I cannot get sales last year per day and if I create one visual and select multiple week, if i have weeks display I get correct amounts but not total and if i want only a total for filtered weeks i get again a wrong number. Majority of connection in the datamodel for date are made through D_Periode using Dato which is format as dd/mm/yyyy Thank you in advance.
Getting all subsequent sales after slicer choice
Dear forum, It will be my first post here after working for over 4 years with PowerBI. I came across a challenge which I can't seem to solve by myself so I could really use your help! The premis I have created a very simple (fictive) dataset to define my challenge. The dataset contains two tables: - Products which is a lookup table ProductName ProductID ProductGroup Product A 1 Group A Product B 2 Group A Product C 3 Group A Product D 4 Group A Product E 5 Group B Product F 6 Group B Product G 7 Group B Product H 8 Group B - Sales is the table where all the sold products are registered per customer ProductID CustomerID Sequence SaleDate 1 1 2 2022-02-01 2 1 3 2022-03-01 3 1 4 2022-04-01 4 1 1 2022-01-01 7 1 5 2022-05-01 6 2 3 2022-04-01 7 2 4 2022-06-01 8 2 1 2022-01-01 2 2 2 2022-03-01 6 3 1 2022-06-01 2 3 2 2022-07-01 5 4 5 2022-10-01 8 4 2 2022-07-01 2 4 1 2022-06-01 4 4 3 2022-08-01 1 4 4 2022-09-01 Here a PowerBI datamodel view for the relation between the two: The challenge I want to have a slicer on productname and once the user has selected a product, i would like to see the subsequent productsales that have been done. Here is a visualisation of what i am tryng to accomplish: The multicolored table is the Sales table with three scenarios. The yellow table is the result of the slicer choice The green table is the desired output I have tried a dynamic table and also tried to search for comparing scenario's online but was not succesfull. Here is the link to the source excel file and pbix file. PowerBI and Excel file Thanks in advanced! With regards, Anders Donker
