To show the actual and budget value on table

[GayatriG1]

On selection of particual month on slicer I want to show the actual value upto the selected month and for rest of the month I only want to show budget value.

Ex. If I select month May on slicer then it should show only actual value form Jan to May and budget value form Jun to Dec .

Which DAX function I need to use for this and what will be the logic 

[v-yueyunzh-msft · Accepted solution]

Hi , @GayatriG1 

Accoridng to your description, you want to add a slicer to split the "Actual" and the "Budget".

Here are the steps you can refer to :
(1)This is my test data:

(2)We can add a slicer like this:

(3)We can create a measure:

Measure = var _slicer = [Parameter Value]
var _cur_flag = MAX('Table'[Flag])
var _cur_date = MAX('Table'[Date])
return
IF(_cur_flag="Actual" && MONTH(_cur_date)<= _slicer , SUM('Table'[Value]) , IF(_cur_flag="Budget" && MONTH(_cur_date)>_slicer , SUM('Table'[Value]) ,BLANK()))

(4)Then we can put it on the visual like this:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi, @GayatriG1 

Thanks for your response and explanation. 

Here are the steps you can refer to :

(1)We need update the 'YTD 21' table 's [Month] column to date type.

(2)We need to create a table like this:

For the last 2 column , we can not dynamic generate the column , so i fix the name like "YTD Actual 21-Month".

(3)Then we can create a measure like this:

Measure = var _slicer_month = MONTH( MAX('Date'[Month]))

var _budge_month = IF(_slicer_month<=6 , _slicer_month ,6)

var _slicer_last_year = DATE(2021,_budge_month,1)

var _column_flag = MAX('Table'[Flag])

var _column_date = MAX('Table'[Date])

var _column_name = MAX('Table'[Name])

var _total = CALCULATE( SUM('Invoked Function'[Total]) , 'Invoked Function'[Flag]=_column_flag ,YEAR( 'Invoked Function'[Month]) = YEAR( _column_date) , MONTH( 'Invoked Function'[Month]) = MONTH( _column_date) )

var _ytd21 = CALCULATE( SUM('YTD 21'[Value]) , TREATAS({_slicer_last_year},'YTD 21'[Month]) ,TREATAS(VALUES('Invoked Function'[Contract-Development]),'YTD 21'[Contract-Development]))

var _ytd_actual22 = CALCULATE(SUM('Invoked Function'[Total]) , FILTER( 'Invoked Function' , 'Invoked Function'[Flag]="Actual" && MONTH('Invoked Function'[Month])<= _budge_month))

var _ytd_budget22 = CALCULATE(SUM('Invoked Function'[Total]) , FILTER( 'Invoked Function' , 'Invoked Function'[Flag]="Budget" && MONTH('Invoked Function'[Month])> _budge_month))

return

IF(_column_flag="Actual" && _column_date <> BLANK() && MONTH(_column_date)<=_slicer_month , _total,

IF(_column_flag="Budget" && _column_date <> BLANK() && MONTH(_column_date)>_budge_month,_total ,

IF(_column_name= "YTD Actual 21-Month" , _ytd21,

IF(_column_name="YTD Actual 22-Month" ,_ytd_actual22 ,

IF(_column_name="YTD 22-Dec",_ytd_budget22+_ytd_actual22,BLANK())

)

)))

(4)Then we put the measure on the visual and we can meet your need:

By another, the slicer field i used is the 'Date' Table:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi , @GayatriG1 

If you just want to show the three summrized value , you can create three measures and just put the measures on the visual. But for the measure name we can not dynamic generated according to the slicer visual.

The three  measures are this:

YTD 21 Month = 
var _slicer_month = MONTH( MAX('Calendar'[Date]))
var _budge_month = IF(_slicer_month<=6 , _slicer_month ,6)
var _slicer_last_year = DATE(2021,_budge_month,1)
return
CALCULATE( SUM('TB-YTD21'[YTD Total]) , TREATAS({_slicer_last_year},'TB-YTD21'[Date]) ,TREATAS(VALUES('TB-Budget'[Contract-Development]),'TB-YTD21'[Contract-Development]))

YTD 22 Actual = 
var _slicer_month = MONTH( MAX('Calendar'[Date]))
var _budge_month = IF(_slicer_month<=6 , _slicer_month ,6)
return
 CALCULATE(SUM('TB-Budget'[Total]) , FILTER( 'TB-Budget' ,'TB-Budget'[Flag]="Actual" &&  MONTH('TB-Budget'[Date])<= _budge_month))

YTD Dec 22 = 
 var _slicer_month = MONTH( MAX('Calendar'[Date]))
var _budge_month = IF(_slicer_month<=6 , _slicer_month ,6)
 var _ytd_budget22 =  CALCULATE(SUM('TB-Budget'[Total]) , FILTER( 'TB-Budget' , 'TB-Budget'[Flag]="Budget" && MONTH('TB-Budget'[Date])> _budge_month))
 return
 _ytd_budget22+[YTD 22 Actual]

The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi , @GayatriG1 

Please try this :

Measure Test 2 = var _slicer_month = MONTH( MAX('Calendar'[Date]))
var _budge_month = IF(_slicer_month<=6 , _slicer_month ,6)
var _slicer_last_year = DATE(2021,_budge_month,1)
var _column_flag = MAX('Table'[Flag])
var _column_date = MAX('Table'[Date])
var _column_name = MAX('Table'[Name])
var _total = CALCULATE( SUM('TB-Budget'[Total]) , 'TB-Budget'[Flag]=_column_flag ,YEAR('TB-Budget'[Date]) = YEAR( _column_date) , MONTH('TB-Budget'[Date]) = MONTH( _column_date) )
var _ytd21 = CALCULATE( SUM('TB-YTD21'[YTD Total]) , TREATAS({_slicer_last_year},'TB-YTD21'[Date]) ,TREATAS(VALUES('TB-Budget'[Contract-Development]),'TB-YTD21'[Contract-Development]))
var _ytd_actual22 =  CALCULATE(SUM('TB-Budget'[Total]) , FILTER( 'TB-Budget' ,'TB-Budget'[Flag]="Actual" &&  MONTH('TB-Budget'[Date])<= _budge_month))
var _ytd_budget22 =  CALCULATE(SUM('TB-Budget'[Total]) , FILTER( 'TB-Budget' , 'TB-Budget'[Flag]="Budget" && MONTH('TB-Budget'[Date])> _budge_month))
return
IF(_column_flag="Actual" && _column_date <> BLANK() && MONTH(_column_date)<=_slicer_month , _total, 
IF(_column_flag="Budget" && _column_date <> BLANK() && MONTH(_column_date)>_budge_month,_total ,
IF(_column_name= "YTD Actual-21 Months" , _ytd21,
IF(_column_name="YTD Actual-22 Months" ,_ytd_actual22 ,
IF(_column_name="YTD Dec-22",_ytd_budget22+_ytd_actual22,BLANK())
)
)))

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-yueyunzh-msft · Accepted solution]

Hi, @GayatriG1 

Here is the .pbix file i test in my side.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[GayatriG1]

Hi Aniya,

Need your help here, 

I need to put YTD 21 Month,  YTD 22 Actual  and  YTD Dec 22 in single row in the visuals.  With no. 1 position should be YTD 21 Month , 2nd position YTD 22 Actual and 3rd postion all Actual and Budgeted figures and at last it should show the YTD Dec 22 . 

all these fields should be in 1 line as mention above Position no 1 2 3 4 the value should be in one line .

https://drive.google.com/file/d/1AveaIH_5_srXgpG5YRMpyWkr40GtuOhv/view?usp=share_link

Regards,

Gayatri

[GayatriG1]

Hi Aniya,

Big big thank you for your help. Appriciate your support.

Regards,

Gayatri

[jolind1996]

Hello, GayatriG1

It depends on how you pick dates in your report. Is it month-based or day-based? The solution below suggest building an "inverse" YTD measure from scratch (day-based): Calculating Remaining Budget for rest of year? - Microsoft Power BI Community

Hope it helps.

Kind regards,
Johannes