Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
AaronO23
Frequent Visitor

Guage Visual - Actuals to Date vs Budget to Date with FY Budget as Max.

Hello,

 

I am trying to create a Guage visual that has the Revenue to date as Actuals, Budget to date as Target and Total budget for the full year as the Maximum value. I require the actuals/budget to date to be dynamic based on a date slicer I have, and all 3 variables to be dynamic based on a Sector slicer I have.

 

Is there a way to get this to work as I am struggling with getting the maximum value to stay as the full year budget (but change based on Sector picked)? Currently I either get it where it is also dynamic with the date slicer (so only shows YTD), or it shows the full year for all Sectors as a whole, even if only one selected in the slicer.

 

Thank you

1 ACCEPTED SOLUTION
rajendraongole1
Super User
Super User

Hi @AaronO23 -  Create a measures will be filtered based on the selected date range from the date slicer (Actual to Date and Budge to Date measures as follows:

 

Actuals_ToDate =
CALCULATE(
SUM('YourTable'[Revenue]),
FILTER(
'YourTable',
'YourTable'[Date] <= MAX('DateTable'[Date])
)
)

Budget_ToDate =
CALCULATE(
SUM('YourTable'[Budget]),
FILTER(
'YourTable',
'YourTable'[Date] <= MAX('DateTable'[Date])
)
)

 

To ignore the date slicer but still respect the sector slicer create another measure as below for total year budget

 

Total_Year_Budget =
CALCULATE(
SUM('YourTable'[Budget]),
ALL('DateTable')
)

 

 

 

using above measure and also placing the created fields in actuals, targets and maximum value with Total year budget. you will see the dynamic Gauge visual in Power BI that adjusts based on both date and sector slicers

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
rajendraongole1
Super User
Super User

Hi @AaronO23 -  Create a measures will be filtered based on the selected date range from the date slicer (Actual to Date and Budge to Date measures as follows:

 

Actuals_ToDate =
CALCULATE(
SUM('YourTable'[Revenue]),
FILTER(
'YourTable',
'YourTable'[Date] <= MAX('DateTable'[Date])
)
)

Budget_ToDate =
CALCULATE(
SUM('YourTable'[Budget]),
FILTER(
'YourTable',
'YourTable'[Date] <= MAX('DateTable'[Date])
)
)

 

To ignore the date slicer but still respect the sector slicer create another measure as below for total year budget

 

Total_Year_Budget =
CALCULATE(
SUM('YourTable'[Budget]),
ALL('DateTable')
)

 

 

 

using above measure and also placing the created fields in actuals, targets and maximum value with Total year budget. you will see the dynamic Gauge visual in Power BI that adjusts based on both date and sector slicers

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





This is great, thank you. As a separate problem but on the same visual, do you know how to fix the issue with negative values. I have some sectors that have both a negative actual and budget (as they are pure cost centres), and so when I filter to these sectors, the guage visual won't actually show any bar fill at all (see below visual).

 

AaronO23_1-1718034591645.png

 

 

Hi @AaronO23 - create below measures on actualto date, budget to date, and fullyearbudget with ABS function.

PositiveActualsToDate = ABS([ActualsToDate])
PositiveBudgetToDate = ABS([BudgetToDate])
PositiveFullYearBudget = ABS([FullYearBudget])

 

use the above measures in your guage chart in below fields.

Actual Value: PositiveActualsToDate
Target Value: PositiveBudgetToDate
Maximum Value: PositiveFullYearBudget

 

Try it and let know.

 

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.