Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Hi,
Share the link from where i can download your PBI file. Show the Table there where your result is 0.
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |