Forum Discussion
DAX Use Case for Dynamic Trending Reports
Community SupportAnonymous ,
My problem is that the result will potentially differ at any given daily runtime, but the sum total on any given day in history will remain valid. I need to trend sum of QtyOnHand over days. The following table shows the daily sum across LotNumbers for the above data set both with and without the filter at any given runtime. I can do this easily in Excel, but I’m not sure how to achieve the same in PBI, but I expect it could be done using a DAX expression with appropriate filtering to generate a custom measure:
Could you please charify more details about "trend sum of QtyOnHand over days"?
Regards,
Jimmy Tao
"Corrected TOTAL" in my example is the sum of all QtyOnHand where MaxAsOfDate = 1 in the first table (calculated column expression defines). "TOTAL" is just the sum of all QtyOnHand on any given date, which is what I get now. I need to get the "Corrected TOTAL" for all dates, which is the result in the second table (generated using Excel from values in the first table)). Each sum in the second table is generated by getting MaxAsOfDate on any given day (I include only two examples in the first table, but second table has all 16 date results). I'm not sure how to get the results using PBI (generate the second table and chart I posted in PBI). The only difference between TOTAL and Corrected TOTAL sums is that the latter gets rid of QtyOnHand rows for any given LotNumber that has an updated AsOfDateKey at any given runtime.