adjusting DAX measure for opening balance

[Anonymous]

Would someone please tell me how I can edit the following 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)

This is the result I'm getting in Power BI (it does the calculation correctly, but only at the beginning of each month):

I need it to show me the 'Total Budget per ZP' value/column as follows (the calculation should happen line by line, in a chronological order):

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

So, the only filter that should apply is the Project Number ('nummer') in column 3.

Looking forward to your response.

Thanks.

[amitchandak]

@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])))

[Anonymous]

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.

[littlemojopuppy]

Change your code to this...

var Planned_budget = SUM(DIA_PBI_SAP_ZEITPROJECTAUFGABEN[projectitembudgettime])

var maxSelectedDate = MAX('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] < maxSelectedDate), 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)

[Anonymous]

Hi @littlemojopuppy 

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.