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 PBI Experts,
I need your help with creation fo measure to calculate amount if end date is in "next year". I have start date and end date (which is actually is start date + 12 moths), name, amount and one more date. E.g. If end date is for exammple in Ferbuary 2017, then for January and February add these amounts to total by month.
Sampe table with data and expected results below:
name | strat date | end date | amount | amount_date | what we should sum in 2017 |
Chris | 6/1/2016 | 5/31/2017 | 1000 | 6/1/2016 | |
Chris | 6/1/2016 | 5/31/2017 | 1000 | 7/1/2016 | |
Chris | 6/1/2016 | 5/31/2017 | 1000 | 8/1/2016 | |
Chris | 6/1/2016 | 5/31/2017 | 1000 | 9/1/2016 | |
Chris | 6/1/2016 | 5/31/2017 | 1500 | 10/1/2016 | |
Chris | 6/1/2016 | 5/31/2017 | 1500 | 11/1/2016 | |
Chris | 6/1/2016 | 5/31/2017 | 1500 | 12/1/2016 | |
Chris | 6/1/2016 | 5/31/2017 | 1500 | 1/1/2017 | 1500 |
Chris | 6/1/2016 | 5/31/2017 | 1500 | 2/1/2017 | 1500 |
Chris | 6/1/2016 | 5/31/2017 | 1500 | 3/1/2017 | 1500 |
Chris | 6/1/2016 | 5/31/2017 | 1500 | 4/1/2017 | 1500 |
Chris | 6/1/2016 | 5/31/2017 | 2000 | 5/1/2017 | 2000 |
Chris | 6/1/2016 | 5/31/2017 | 2000 | 6/1/2017 | |
Jenna | 12/1/2016 | 11/30/2017 | 2000 | 12/1/2016 | |
Jenna | 12/1/2016 | 11/30/2017 | 2000 | 1/1/2017 | 2000 |
Jenna | 12/1/2016 | 11/30/2017 | 2000 | 2/1/2017 | 2000 |
Jenna | 12/1/2016 | 11/30/2017 | 2000 | 3/1/2017 | 2000 |
Jenna | 12/1/2016 | 11/30/2017 | 2000 | 4/1/2017 | 2000 |
Jenna | 12/1/2016 | 11/30/2017 | 2000 | 5/1/2017 | 2000 |
Jenna | 12/1/2016 | 11/30/2017 | 2000 | 6/1/2017 | 2000 |
Ilon | 1/1/2017 | 12/31/2017 | 500 | 1/1/2017 | 500 |
Ilon | 1/1/2017 | 12/31/2017 | 500 | 2/1/2017 | 500 |
Ilon | 1/1/2017 | 12/31/2017 | 500 | 3/1/2017 | 500 |
Ilon | 1/1/2017 | 12/31/2017 | 500 | 4/1/2017 | 500 |
Ilon | 1/1/2017 | 12/31/2017 | 500 | 5/1/2017 | 500 |
Ilon | 1/1/2017 | 12/31/2017 | 300 | 6/1/2017 | 300 |
Ilon | 1/1/2017 | 12/31/2017 | 300 | 7/1/2017 | 300 |
Ilon | 1/1/2017 | 12/31/2017 | 400 | 8/1/2017 | 400 |
Ilon | 1/1/2017 | 12/31/2017 | 400 | 9/1/2017 | 400 |
Ilon | 1/1/2017 | 12/31/2017 | 650 | 10/1/2017 | 650 |
Ilon | 1/1/2017 | 12/31/2017 | 700 | 11/1/2017 | 700 |
result for 2017
month | result for each month |
1/1/2017 | 4000 |
2/1/2017 | 4000 |
3/1/2017 | 4000 |
4/1/2017 | 4000 |
5/1/2017 | 4500 |
6/1/2017 | 2300 |
7/1/2017 | 300 |
8/1/2017 | 400 |
9/1/2017 | 400 |
10/1/2017 | 650 |
11/1/2017 | 700 |
12/1/2017 | 0 |
I've created measure for calculation such sum, which does not work well, could you please help me correct it or provide better solution.
test2 = CALCULATE(SUM('table1'[amount]),
FILTER('table1','table1'[tets_end date] <= DATE(2017,12,1)),
FILTER('table1', 'table1'[tets_end date] >= DATE(2017,1,1)))
Solved! Go to Solution.
Hi all,
Found other solution (calculated column):
Column = IF(AND(Table1[amount_date] >= DATE(Table1[year-amount_date],1,1),
AND(Table1[amount_date] <= DATE(Table1[year-amount_date],12,31),
Table1[amount_date] < Table1[end date])),Table1[amount],0)
Hi all,
Found other solution (calculated column):
Column = IF(AND(Table1[amount_date] >= DATE(Table1[year-amount_date],1,1),
AND(Table1[amount_date] <= DATE(Table1[year-amount_date],12,31),
Table1[amount_date] < Table1[end date])),Table1[amount],0)
By the way, to help close this thread, just accept the solution above. Your contribution is highly appreciated.
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |