Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have been trying to write a measure that can calculate the sum of a qty column based on date values in the past and in the future per month. The months should start from current month until the end which is always august.
planned date | qty |
1/1/2022 | 12 |
1/2/2022 | 14 |
1/3/2022 | 123 |
1/4/2022 | 66 |
1/5/2022 | 55 |
1/6/2022 | 55 |
1/7/2022 | 98 |
1/8/2022 | 23 |
1/9/2022 | 78 |
2/1/2022 | 45 |
2/2/2022 | 19 |
1/1/2023 | 76 |
2/1/2023 | 18 |
12/11/2023 | 73 |
The results I expect are,
DEC | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG |
5 | 18+5 | 23+16 | . | . | . | . | . | . |
I've tried the SQLbi tutorial, but that doesn't seem to work, some assistance is greatly appreciated.
OutstandingAmountPerMonth =
VAR LastVisibleDate =
MAX('Date'[Date])
VAR FirstVisibleDate =
MIN('Date'[Date])
VAR LastDateWithOutstanding =
CALCULATE(
MAX(Sales_Line[Planned Shipment Date]),
REMOVEFILTERS()
)
VAR Result =
IF( FirstVisibleDate < LastDateWithOutstanding,
CALCULATE( SUM( Sales_Line[Outstanding Amount]), 'Date'[Date] <= LastVisibleDate, Sales_Line[Type_(sales_order_assignment)] = BLANK() ) )
RETURN
Result
Thanks for reaching out to us.
It seems there is no relationship between sample data and expected output, so it is difficult to understand how your expected result is calculated. Theoretically, the cumulative total is the sum of the current month and the previous total. Please reshare your expected output, thanks
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |