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.
Jahr | Calendar Month | nummer | AufgabeCode | Pauschal/Aufwand | Externe/Interne | Total Project Budget | Total Budget per ZP | Eröffnung Budget | dauerdezimal | Verrechenbar | Nicht Verrechenbar | Abschluss Budget |
2017 | Dec | 2830 | 58552 | Pauschal | Externe | 346 | 346 | 52 | 4.5 | 4.5 | 0 | 47.5 |
2017 | Dec | 2830 | 58553 | Pauschal | Externe | 346 | 341.5 | 94 | 12 | 12 | 0 | 82 |
2018 | Jan | 2830 | 58552 | Pauschal | Externe | 346 | 329.5 | 47.5 | 1.25 | 1.25 | 0 | 46.25 |
2018 | Jan | 2830 | 58553 | Pauschal | Externe | 346 | 328.25 | 82 | 28.25 | 27.5 | 0.75 | 54.5 |
2018 | Jan | 2830 | 58556 | Pauschal | Externe | 346 | 300.75 | 12 | 1.5 | 1.5 | 0 | 10.5 |
2018 | Feb | 2830 | 58552 | Pauschal | Externe | 346 | 299.25 | 46.25 | 1.5 | 1.5 | 0 | 44.75 |
2018 | Feb | 2830 | 58553 | Pauschal | Externe | 346 | 297.75 | 54.5 | 0.5 | 0.5 | 0 | 54 |
2018 | Feb | 2830 | 58555 | Pauschal | Externe | 346 | 297.25 | 76 | 12 | 12 | 0 | 64 |
2018 | Feb | 2830 | 58556 | Pauschal | Externe | 346 | 285.25 | 10.5 | 0.75 | 0 | 0.75 | 10.5 |
2018 | Mar | 2830 | 58552 | Pauschal | Externe | 346 | 285.25 | 44.75 | 4.5 | 4.5 | 0 | 40.25 |
2018 | Mar | 2830 | 58556 | Pauschal | Externe | 346 | 280.75 | 10.5 | 6 | 6 | 0 | 4.5 |
2018 | Apr | 2830 | 58552 | Pauschal | Externe | 346 | 274.75 | 40.25 | 2 | 2 | 0 | 38.25 |
2018 | Apr | 2830 | 58553 | Pauschal | Externe | 346 | 272.75 | 54 | 13.75 | 12 | 1.75 | 42 |
2018 | May | 2830 | 58553 | Pauschal | Externe | 346 | 260.75 | 42 | 1.25 | 1.25 | 0 | 40.75 |
2018 | May | 2830 | 58556 | Pauschal | Externe | 346 | 259.5 | 4.5 | 0.25 | 0.25 | 0 | 4.25 |
2018 | Jun | 2830 | 58553 | Pauschal | Externe | 346 | 259.25 | 40.75 | 3 | 3 | 0 | 37.75 |
2018 | Aug | 2830 | 58554 | Pauschal | Externe | 346 | 256.25 | 112 | 8.25 | 8.25 | 0 | 103.75 |
2018 | Sep | 2830 | 58554 | Pauschal | Externe | 346 | 248 | 103.75 | 5.5 | 5.5 | 0 | 98.25 |
2018 | Oct | 2830 | 58554 | Pauschal | Externe | 346 | 242.5 | 98.25 | 37.25 | 34 | 3.25 | 64.25 |
2018 | Nov | 2830 | 58554 | Pauschal | Externe | 346 | 208.5 | 64.25 | 17.5 | 17.5 | 0 | 46.75 |
2018 | Dec | 2830 | 58554 | Pauschal | Externe | 346 | 191 | 46.75 | 4 | 4 | 0 | 42.75 |
2019 | Feb | 2830 | 58554 | Pauschal | Externe | 346 | 187 | 42.75 | 9.25 | 9.25 | 0 | 33.5 |
2019 | Feb | 2830 | 58556 | Pauschal | Externe | 346 | 177.75 | 4.25 | 1.75 | 1.75 | 0 | 2.5 |
2019 | Mar | 2830 | 58554 | Pauschal | Externe | 346 | 176 | 33.5 | 32 | 31.5 | 0.5 | 2 |
2019 | Apr | 2830 | 58554 | Pauschal | Externe | 346 | 144.5 | 2 | 2 | 2 | 0 | 0 |
2019 | Sep | 2830 | 58554 | Pauschal | Externe | 346 | 142.5 | 0 | 5.75 | 5.25 | 0.5 | -5.25 |
@Anonymous , Assuming you date in the table , based on sample try a measure like
sum(Table[Total Project Budget]) - calculate(sum(Table[dauerdezimal]), filter(all(Table), Table[Date] <=max(Table[Date])))
or
sum(Table[Total Project Budget]) - calculate(sum(Table[dauerdezimal]), filter(allselected(Table), Table[Date] <=max(Table[Date])))
Hi,
Neither of them works I'm afraid.
Can you please make an adjustment to my current DAX?:
Total Budget per ZP =
var Planned_budget = SUM(DIA_PBI_SAP_ZEITPROJECTAUFGABEN[projectitembudgettime])
var minSelectedDate = MIN('Date'[Date])
var raportiertezeit = sum(DIA_SAP_LEISTUNGEN_AUS_SAP[dauerdezimal])
var AllActualWorkDoneBeforeCurrentDate =
CALCULATE(
SUM(DIA_SAP_LEISTUNGEN_AUS_SAP[Verrechenbar1]),
FILTER(ALL('Date'[Date]), [Date] < minSelectedDate), ALL(DIA_SAP_LEISTUNGEN_AUS_SAP[AufgabeCode], DIA_SAP_LEISTUNGEN_AUS_SAP[Externe/Interne]), ALL(Artikelkategorien[Pauschal/Aufwand])
)
return
if (raportiertezeit <> BLANK(),Planned_budget-AllActualWorkDoneBeforeCurrentDate)
BTW, i am using a date from a dedicated date table: 'Date'[Date]
Looking forward to hearing back from you.
Many thanks.
Change your code to this...
Thanks for your response.
Unfortunately, it still doesn't work.
This is the result of your DAX:
Here is how it should be:
Jahr | Calendar Month | nummer | AufgabeCode | Pauschal/Aufwand | Externe/Interne | Total Project Budget | Total Budget per ZP | Total Budget per ZP2 | Should be | dauerdezimal | Verrechenbar |
2017 | Dec | 2830 | 58552 | Pauschal | Externe | 346 | 346 | 329.5 | 346 | 4.5 | 4.5 |
2017 | Dec | 2830 | 58553 | Pauschal | Externe | 346 | 346 | 329.5 | 341.5 | 12 | 12 |
2018 | Jan | 2830 | 58552 | Pauschal | Externe | 346 | 329.5 | 299.25 | 329.5 | 1.25 | 1.25 |
2018 | Jan | 2830 | 58553 | Pauschal | Externe | 346 | 329.5 | 299.25 | 328.25 | 28.25 | 27.5 |
2018 | Jan | 2830 | 58556 | Pauschal | Externe | 346 | 329.5 | 299.25 | 300.75 | 1.5 | 1.5 |
2018 | Feb | 2830 | 58552 | Pauschal | Externe | 346 | 299.25 | 285.25 | 299.25 | 1.5 | 1.5 |
2018 | Feb | 2830 | 58553 | Pauschal | Externe | 346 | 299.25 | 285.25 | 297.75 | 0.5 | 0.5 |
2018 | Feb | 2830 | 58555 | Pauschal | Externe | 346 | 299.25 | 285.25 | 297.25 | 12 | 12 |
2018 | Feb | 2830 | 58556 | Pauschal | Externe | 346 | 299.25 | 285.25 | 285.25 | 0.75 | 0 |
2018 | Mar | 2830 | 58552 | Pauschal | Externe | 346 | 285.25 | 274.75 | 285.25 | 4.5 | 4.5 |
2018 | Mar | 2830 | 58556 | Pauschal | Externe | 346 | 285.25 | 274.75 | 280.75 | 6 | 6 |
2018 | Apr | 2830 | 58552 | Pauschal | Externe | 346 | 274.75 | 260.75 | 274.75 | 2 | 2 |
2018 | Apr | 2830 | 58553 | Pauschal | Externe | 346 | 274.75 | 260.75 | 272.75 | 13.75 | 12 |
2018 | May | 2830 | 58553 | Pauschal | Externe | 346 | 260.75 | 259.25 | 260.75 | 1.25 | 1.25 |
2018 | May | 2830 | 58556 | Pauschal | Externe | 346 | 260.75 | 259.25 | 259.5 | 0.25 | 0.25 |
2018 | Jun | 2830 | 58553 | Pauschal | Externe | 346 | 259.25 | 256.25 | 259.25 | 3 | 3 |
2018 | Aug | 2830 | 58554 | Pauschal | Externe | 346 | 256.25 | 248 | 256.25 | 8.25 | 8.25 |
2018 | Sep | 2830 | 58554 | Pauschal | Externe | 346 | 248 | 242.5 | 248 | 5.5 | 5.5 |
2018 | Oct | 2830 | 58554 | Pauschal | Externe | 346 | 242.5 | 208.5 | 242.5 | 37.25 | 34 |
2018 | Nov | 2830 | 58554 | Pauschal | Externe | 346 | 208.5 | 191 | 208.5 | 17.5 | 17.5 |
2018 | Dec | 2830 | 58554 | Pauschal | Externe | 346 | 191 | 187 | 191 | 4 | 4 |
2019 | Feb | 2830 | 58554 | Pauschal | Externe | 346 | 187 | 177 | 187 | 9.25 | 9.25 |
2019 | Feb | 2830 | 58556 | Pauschal | Externe | 346 | 187 | 177 | 177.75 | 1.75 | 1.75 |
2019 | Mar | 2830 | 58554 | Pauschal | Externe | 346 | 176 | 144.5 | 176 | 32 | 31.5 |
2019 | Apr | 2830 | 58554 | Pauschal | Externe | 346 | 144.5 | 142.5 | 144.5 | 2 | 2 |
2019 | Sep | 2830 | 58554 | Pauschal | Externe | 346 | 142.5 | 137.25 | 142.5 | 5.75 | 5.25 |
So the idea is that we should take the 'Total Project Budget' figure and subtract all 'Verrechenbar' values from previous line/row (the focus should be on the calculation performing row by row, because sometimes we would have more than one entry in the same period, as is the case above for Dec 2017/Jan 2018 etc.).
Is this something doable
Cheers.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |