Forum Discussion

brittney's avatar
brittney
Frequent Visitor
7 years ago

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-msft's avatar
    v-danhe-msft
    Microsoft 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

    • brittney's avatar
      brittney
      Frequent 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. 

  • Hi,

     

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