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,
I've got this measure to create:
show accumulated costs only in the month when the project is delivered
project table:
projectid deliverymonth
1 201702
2 201703
costs table
month projectid amount
201701 1 100
201702 1 200
201703 1 300
201701 2 111
201702 2 222
201703 2 333
result should show:
projectid month acc.costs at delivery
1 201701 0
1 201702 200
1 201703 0
2 201701 0
2 201702 0
2 201703 666
If tried sumx and if options, but no luck so far.
Thanks,
Johan.
Solved! Go to Solution.
I assume your two tables linked by Project Id. Then you just need to add a calculated column like below:
Cumulative = IF ( Costs[Month] = RELATED ( Project[DeliveryMonth] ), CALCULATE ( SUM ( Costs[Amount] ), FILTER ( Costs, Costs[Month] <= EARLIER ( Costs[Month] ) && Costs[Project] = EARLIER ( Costs[Project] ) ) ), 0 )
Regards,
I assume your two tables linked by Project Id. Then you just need to add a calculated column like below:
Cumulative = IF ( Costs[Month] = RELATED ( Project[DeliveryMonth] ), CALCULATE ( SUM ( Costs[Amount] ), FILTER ( Costs, Costs[Month] <= EARLIER ( Costs[Month] ) && Costs[Project] = EARLIER ( Costs[Project] ) ) ), 0 )
Regards,
Thanks.
through the msdn network I found a calculated measure :
if(maxx(month) = max(project[deliverymonth); [accumulated value];0)
where [accumulated value] is the regular dax formula for accumulations.
I like your solution because for performance reasons it might be better to put into the table as new column. Though I think it should be a column in the project table, since there are multiple cost lines, which would duplicate the value since in the month of delivery you can have multiple lines calculating the same column value.
Thanks for your input!
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |