Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |