Subtotal for measure calculating changes over time using date slicer

[brittney]

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!

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Show the Table there where your result is 0.

[v-danhe-msft]

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

[brittney]

@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.