Opening balance problem in January month calculating retained earnings

Justas — Mon, 30 Nov 2020 13:11:18 GMT

I have 3 dax measures in balance sheet calculating Retained earnings of the current year:

1) Opening balance Retained earnings

2) Turnover (increase/decrease) Retained earnings of selected month

3) Closing balanse Retained earnings

For Opening balance I use YTD formula -1 period

CALCULATE([Pelnas prieš mokesčius YTD],DATEADD(Kalendorius[Data],-1,MONTH),KEEPFILTERS(F_B[F/B1]="F"))

For Closing balance I use just YTD formula

CALCULATE([Pelnas prieš mokesčius],DATESYTD(Kalendorius[Data],"12-31"),KEEPFILTERS(F_B[F/B1]="F"))

It works perfect except January month Opening balance returns previuos year December month.

My problem is to write if statement, which should behave by thuther logic:

if selected value in slicer is January then return value 0 ( or Blank()) else return Retained earnings YTD - 1 period.

My try:

Pelnas prieš mokesčius YTD -1 Month =

Var EBTrodiklis =

CALCULATE([Pelnas prieš mokesčius YTD],DATEADD(Kalendorius[Data],-1,MONTH),KEEPFILTERS(F_B[F/B1]="F"))

Return

if(ISFILTERED(Kalendorius[Data].[Month])="1",0,EBTrodiklis) =>does not work

Pelnas prieš mokesčius YTD -1 Month =

Var EBTrodiklis =

CALCULATE([Pelnas prieš mokesčius YTD],DATEADD(Kalendorius[Data],-1,MONTH),KEEPFILTERS(F_B[F/B1]="F"))

Return

if(SELECTEDVALUE(Kalendorius[Data])="1",0,EBTrodiklis) =>

=>does not work

Pelnas prieš mokesčius YTD -1 Month =

Var EBTrodiklis =

CALCULATE([Pelnas prieš mokesčius YTD],DATEADD(Kalendorius[Data],-1,MONTH),KEEPFILTERS(F_B[F/B1]="F"))

Return

if(month( =>not allows acces to calendar table

Please help!

Re: Opening balance problem in January month calculating retained earnings

amitchandak — Mon, 30 Nov 2020 11:56:45 GMT

Justas , if the closing balance is the end of the period then you can try closingbalancemonth, closingbalancequarter,closingbalanceyear - refer https://youtu.be/yPQ9UV37LOU

for opening, balance refer -https://youtu.be/6lzYOXI5wfo

topic Opening balance problem in January month calculating retained earnings in DAX Commands and Tips

Opening balance problem in January month calculating retained earnings

Re: Opening balance problem in January month calculating retained earnings