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.
Hello,
I have a dataset with 3 columns : PRODUCT, DAY and QUANTITY.
I want to set up a pivot table that displays :
I created a measure to calculate the Cumulative quantity :
CUMULATIVE :=
VAR CURRENT_DAY = MAX( 'DATASET'[DAY])
RETURN CALCULATE(
SUM('DATASET'[QUANTITY]);
ALL('DATASET'[DAY]);'DATASET'[DAY]<=CURRENT_DAY)
Problem : I have no data for Product 2 on Day 15 ; then my measure can't calculate the cumulative quantity for this product on this day.
Quite easily, I think that the reason is that the CURRENT_DAY variable is blank for Product 2 on Day 15 😫
Does anyone can help me to modify the measure in order to enable the calculation of the Cumulative quantity in this context (i.e even if there is no data) ?
Note that : I need a solution that works in PowerBI and Excel 2016. I have no calendar table in my model, Days are just numbers from 1 to 20.
Solved! Go to Solution.
@jct999
On day 15 for Product 2, there is no record, so the formula cannot produce a result. To achieve what you need, you need to create a dimension table for DAY. Please check the attached file where you will find the solution. You need to modify your formula to point to the dimension table for DAY.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@jct999
On day 15 for Product 2, there is no record, so the formula cannot produce a result. To achieve what you need, you need to create a dimension table for DAY. Please check the attached file where you will find the solution. You need to modify your formula to point to the dimension table for DAY.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |