direct query connection

2 Topics

DAX Running total in Direct Query, but reset to zero each day, if running below zero
Hi Power BI Community, I have an issue, where I want to calculate a running total in a measure, so I'm able to get the primo and ultimo value of the stock quantity. This is a forecast of the stcok 14 days ahead of time, and to be sure that we won't run below zero on a given day. In the event of the running total going below zero, I need it to reset and use the 0 as the new starting point. Backstory I need to use Direct Query as the data source connection. I have therefore limitations that I cannot use a calculated column, since I cannot use CALCULATE in calculated columns due to Direct Query. I have no possibility to throw the calculation out of DAX and Measures and into Power Query, again due to Direct Query limitations. I don't have a possibility to put the calculation outside of DAX, so I need to find a solution within measures, that I can use. Where I'm at right now My data looks a bit like this: Item Date Primo Sales Purchase Ultimo 1 16-08-2024 1.429 771 0 658 1 17-08-2024 658 1136 0 -1136 1 18-08-2024 0 4 384 380 1 19-08-2024 380 594 0 -594 1 20-08-2024 0 630 0 -630 1 21-08-2024 0 659 0 -659 1 22-08-2024 0 821 0 -821 1 23-08-2024 0 167 0 -167 1 24-08-2024 0 4 0 -4 1 25-08-2024 0 3 0 -3 1 26-08-2024 0 0 0 0 1 27-08-2024 0 0 0 0 1 28-08-2024 0 0 0 0 1 29-08-2024 0 0 0 0 I have tried to put everything into my ultimo calculation, but I can also see that that is the source of the error. I have 3 measures that's just a sum of the data in the table: WarehouseflowSales = SUM(warehouseflow[sales]) WarehouseflowPurchase = SUM(warehouseflow[purchase]) WarehouseflowInventory = SUM(warehouseflow[inventory]) Then I have the measure for the Ultimo column: WarehouseflowUltimo = VAR MaxDate = MAX(warehouseflow[date]) VAR UltimoYesterday = CALCULATE( -[WarehouseflowSales] + [WarehouseflowPurchase], ALLEXCEPT(warehouseflow, warehouseflow[itemno]), warehouseflow[date] < MaxDate ) + [WarehouseflowInventory] RETURN IF( UltimoYesterday < 0, 0, UltimoYesterday + [WarehouseflowPurchase] - [WarehouseflowSales] ) The measure for the Primo column is just the ultimo value from the day before: WarehouseflowPrimo = VAR MaxDate = MAX(warehouseflow[date]) VAR UltimoYesterday = CALCULATE( [WarehouseflowUltimo], ALLEXCEPT(warehouseflow, warehouseflow[itemno]), warehouseflow[date] = MaxDate - 1 ) RETURN IF( MaxDate = TODAY(), [LagerflowInventory], IF( UltimoYesterday < 0, 0, UltimoYesterday ) ) I have tried to edit the measure to use a somewhat circular reference, but whereas I handle the calculation for the WarehouseflowUltimo measure by date. Meaning that if the the date is today, then I should add the WarehouseflowPurchase and subtract the WarehouseflowSales to the WarehouseflowInventory. If it is not today, then I should use the WarehouseflowPrimo instead of the WarehouseflowInventory. Do you have any suggestions as to how I can handle this? Possible solution I can see myself I have thought of a solution to create 14 different measures, since I need to handle 14 days within my forecast. Then I should be able to handle the issue day by day. I'm just a bit tied on hands and legs due to performance on this issue as well. I haven't tried it yet, but would like to hear your suggestions first 😊
Solved
Anonymous
1 year ago Place DAX Commands and Tips
1.9KViews
0likes
5Comments
Total Sum with Different Columns In Direct Query
Hello, I'm having trouble performing dax formula to give me the total spend for each table (asianamerican,diverse,africanamerican,wbe,divattributes) with the total coming from the fiscspend table with a relationship by the tax id. The Facts table (fiscspnd) is a direct query connection. 1st Question For example, I'm looking to do Total Asian American Spend = Sum or Calculate? Asian Table [ethnicity] = "Asian Amerian"? I also tried to do related with no success. 2nd Question :After having Total Asian American Spend I want to know the % of the Total Spend (this dax formula works) Would just a simple % of Spend =total asianamerican spend / total spend? Thank you for your guidance!
Solved
Anonymous
3 years ago Place DAX Commands and Tips
508Views
0likes
1Comment