Forum Discussion
DAX Use Case for Dynamic Trending Reports
I’m trying to figure out how I might use DAX to programmatically set values for trending time-dependent values for summed counts.
- Multiple DB records with updated lot counts on different dates
- The DB stores all LotNumber counts on days when the count is updated (max frequency one lot count record/day)
- Records with matching LotNumber are essentially duplicates, and the last recorded record is the only valid record for reporting a value on a given date
This is the source data structure with examples of a calculated custom column that I can use to select applicable values to calculate the sum (corrected) total on any given day using a visual filter where MaxAsOfDate = 1. This formula selects only one unique LotNumber count at runtime for all rows occurring at max known AsOfDateKey (there can be multiple rows for a given LotNumber with same AsOfDateKey). The example below shows the calculated value for MaxAsOfDate at 2020-04-01 and 2020-04-16 runtimes (n/a indicates that the record would not have been present at runtime):
MaxAsOfDate = IF(
CALCULATE(
MIN(FactInventory[QtyOnHand]),ALLEXCEPT(FactInventory,FactInventory[LotKey]))
)=MIN(FactInventory[QtyOnHand])
,1,0)
Using simple DAX formula on any given day | ||||
AsOfDateKey | LotNumber | QtyOnHand | MaxAsOfDate | MaxAsOfDate |
20200323 | L 0016 12. | 1,856 | 1 | 1 |
20200325 | L 0037 6. | 3,748 | 1 | 1 |
20200325 | L 0044 10. | 3,713 | 1 | 1 |
20200327 | L 0044 6. | 3,606 | 1 | 1 |
20200327 | L 0044 61. | 3,634 | 1 | 0 |
20200404 | L 0044 61. | 1,648 | 1 | 1 |
20200401 | L 0044 62. | 3,378 | 1 | 0 |
20200414 | L 0044 62. | 2,110 | n/a | 1 |
20200325 | L 0044 7. | 3,887 | 1 | 1 |
20200325 | L 0044 8. | 3,467 | 1 | 1 |
20200411 | L 0065 15. | 2,475 | n/a | 1 |
20200411 | L 0065 3. | 2,661 | n/a | 1 |
20200411 | L 0065 4. | 3,041 | n/a | 1 |
20200411 | L 0065 64. | 3,465 | n/a | 1 |
20200411 | L 0065 9. | 3,047 | n/a | 1 |
20200401 | MH 0044 70. | 2,994 | 1 | 0 |
20200413 | MH 0044 70. | 525 | n/a | 1 |
20200401 | ML 0044 16. | 3,432 | 1 | 1 |
20200327 | ML 0044 18. | 3,582 | 1 | 1 |
20200325 | ML 0044 20. | 3,518 | 1 | 1 |
20200401 | ML 0044 24. | 3,352 | 1 | 1 |
20200327 | ML 0044 65. | 3,653 | 1 | 1 |
20200327 | ML 0044 66. | 4,135 | 1 | 1 |
20200411 | ML 0058 12. | 3,336 | n/a | 0 |
20200416 | ML 0058 12. | 648 | n/a | 1 |
20200411 | ML 0058 15. | 3,360 | n/a | 0 |
20200416 | ML 0058 15. | 2,250 | n/a | 1 |
20200411 | ML 0058 16. | 3,682 | n/a | 1 |
20200411 | ML 0058 54. | 3,830 | n/a | 1 |
20200401 | ML 0065 25. | 2,913 | 1 | 0 |
20200413 | ML 0065 25. | 912 | n/a | 1 |
20200327 | N 0044 31. | 2,453 | 1 | 1 |
20200325 | SL 0037 4. | 3,053 | 1 | 1 |
20200327 | SL 0044 3. | 2,763 | 1 | 1 |
20200401 | SL 0044 60. | 3,231 | 1 | 1 |
20200325 | SSL 0037 1. | 3,353 | 1 | 1 |
20200401 | SSL 0044 1. | 2,768 | 1 | 1 |
20200327 | SSL 0044 56. | 2,569 | 1 | 1 |
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:
Filtering visual on MaxAsOfDate = 1 across all days | ||
x | y | |
Report Day | TOTAL | Corrected TOTAL |
20200401 | 75,058 | 62,139 |
20200402 | 75,058 | 62,139 |
20200403 | 75,058 | 62,139 |
20200404 | 76,706 | 63,787 |
20200405 | 76,706 | 63,787 |
20200406 | 76,706 | 63,787 |
20200407 | 76,706 | 63,787 |
20200408 | 76,706 | 63,787 |
20200409 | 76,706 | 63,787 |
20200410 | 76,706 | 63,787 |
20200411 | 105,603 | 85,988 |
20200412 | 105,603 | 85,988 |
20200413 | 107,040 | 87,425 |
20200414 | 109,150 | 89,535 |
20200415 | 109,150 | 89,535 |
20200416 | 112,048 | 92,433 |
Any assistance would be greatly appreciated.
2 Replies
v-yuta-msftCommunity Support
Anonymous ,
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.