Actual with remaning budget

[ttansriw]

Hi ,

I want to create measure that contains actual with remaining budget and also show in YTD

I have data like this 

Month	Act	Budget
Jan	10	20
Feb	10	20
Mar	10	20
Apr	10	10
May		10
Jun		10
Total	40	90

all I want is to be like this

Month	Act	Budget	Act+Bud	Act+Bud (YTD
Jan	10	20	10	10
Feb	10	20	10	20
Mar	10	20	10	30
Apr	10	10	10	40
May		10	10	50
Jun		10	10	60
Total	40	90	60	60

I tried some method but it doesn't work

Is there any way I can do? Please help. Thank you.

[Jihwan_Kim · Accepted solution]

Hi, @ttansriw 

Please check the below picture and the sample pbix file's link down below.

The measures are in the sample pbix file.

Please be noted that the measures are not considering the year column, because there is no year column in the sample.

If you can share your sample pbix file's link here, I can try to look into it to come up with more accurate measures.

Actual & Budget Remaining =

SUMX (

VALUES ( 'Table'[Month] ),

CALCULATE ( SUM ( 'Table'[Act] ) )

+ CALCULATE ( IF ( SUM ( 'Table'[Act] ) = BLANK (), SUM ( 'Table'[Budget] ) ) )

)

Actual & Budget Remaining YTD =

CALCULATE (

[Actual & Budget Remaining],

FILTER (

ALL ( 'Table' ),

'Table'[Month Number] <= MAX ( 'Table'[Month Number] )

)

)

https://www.dropbox.com/s/2wbln012oeqvpcb/ttansriw.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[ttansriw]

@Jihwan_Kim  Thank you so much!! It works perfectly!! 
😍

[amitchandak]

@ttansriw , Assume you have date. With help from Date table, Month is Date table and time intelligence , please try measures like (3 measures)

Budget M = sum(Table[Budget])

Act M = sum(Table[Act])

Act+Bud (YTD) = calculate(sumx(values('Date'[Month Year]) , if(isblank([Act M]), [Budget M ],[Act M])), datesytd('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[ttansriw]

@amitchandak  Thanks!! I also tried this method, but YTD calculate the actual only. 

and I do have calendar and marked it as date table.

Ps. my data is in the same table . Could this cause the effect that dax doesn't work well ?

Budget M = 

CALCULATE(

SUM(t[Value]),

t[Type] ="bu"

)

Budget M = 

CALCULATE(

SUM(t[Value]),

t[Type] ="act"

)