Forum Discussion
Subtotal for measure calculating changes over time using date slicer
Hi everyone,
I'm currently using a data set that takes a daily snapshot of my data and appends it to the table.
An example of my data is this (create date is the daily snapshot date):
Customer | Qty Ordered | Create Date |
111 | 500 | 11/15/2018 |
111 | 500 | 11/16/2018 |
111 | 400 | 11/17/2018 |
111 | 400 | 11/18/2018 |
111 | 100 | 11/19/2018 |
111 | 0 | 11/20/2018 |
The matrix displayed in the power bi visual is the change of volume between the two dates selected on the date slicer. The delta is calculated with the following measures:
First Date = CALCULATE(MIN('date table[First Date]), ALLSELECTED ('date table'))
QtyFirstOrdered = SUMX(FILTER('QtyArchive', 'QtyArchive[CreateDate] = [First Date]), 'QtyArchive'[Qty - Ordered]) +0
-Same method is used to calculcate the last date quantity ordered (replace MIN with MAX in first measure).
QtyChange = [QtyLastOrdered] - [QtyFirstOrdered]
At this point, everything is calculating as expected. I'm running into issues when I use the measure for QtyChange in other formulas. Example: I need to only Sum the negative qty changes. I've used the following IF statements in measures, and both are correct on a row level, however the column subtotal at the bottom of the matrix is 0:
= IF([QtyChange]<=0,[QtyChange],0)
= IF(QtyChange]<=0,SUMX('QtyArchive',([QtyChange])),0)
What is my measure missing to properly subtotal the negative changes and then continue to build upon the [QtyChange] measure?
Thanks!
3 Replies
- v-danhe-msftMicrosoft Employee
Hi brittney,
From your formula:
First Date = CALCULATE(MIN('date table[First Date]), ALLSELECTED ('date table'))I could not get any data about 'date table', could you please offer me more information about it so I could have a test?
Regards,
Daniel He
- brittneyFrequent Visitor
v-danhe-msft The date table is a table with a single date column built from the distinct "create dates" from the Qty Archive table. The Create Dates are every date the Qty Archive table appends another day of data.
- Ashish_MathurSuper User
Hi,
Share the link from where i can download your PBI file. Show the Table there where your result is 0.