How to dynamically change the max value in the gauge visual?

[euro_sree]

Hi

My daily target turnover is $270000. I have a slicer for the year. The data is from 01/01/2023. I want when only the year 2023 is slected in the slicer the maximum value should come as 365 * 270000 if only 2024 is selected from the slicer then the maximum value should come 365 * 270000 if both the years are selected then 730 * 270000 and so on. If in case nothing is selected in the slicer then it should calculate (Today() - date(2023,1,1)) * 270000

Can somebody please help me to achieve this

[FarhanJeelani · Accepted solution]

Hi @euro_sree ,

To achieve your requirement, you can create a DAX measure that dynamically calculates the maximum value based on the selected year(s) in the slicer. Here’s how to do it:

Steps:

1. Assume You Have a Date Table:

   • Make sure your data model includes a properly set up Date table, with a relationship to your fact table containing turnover data.

2. Create the Measure:

   • Use the following DAX code to create the measure:

   Maximum Target Turnover = 
   VAR SelectedYears = VALUES(DateTable[Year]) -- Get the selected years
   VAR TotalDays =
       IF(
           ISFILTERED(DateTable[Year]),
           SUMX(SelectedYears, 365), -- Calculate total days for selected years
           TODAY() - DATE(2023, 1, 1) -- Days from 01/01/2023 to today
       )
   RETURN
       TotalDays * 270000

3. Explanation:

   • VALUES(DateTable[Year]): Captures the selected years from the slicer.
   • SUMX(SelectedYears, 365): Calculates the total days for all selected years (365 days per year).
   • ISFILTERED(DateTable[Year]): Checks if the slicer has any filter applied; if not, it defaults to calculating the days from 01/01/2023 to TODAY().
   • 270000: The daily target turnover is multiplied by the total days.

4. Add the Measure to Your Visual:

   • Use the measure in a card or visual to display the calculated maximum target turnover.

5. Test the Functionality:

   • Select different years in the slicer and verify that the calculation changes accordingly.
   • If nothing is selected in the slicer, ensure the calculation uses (Today() - DATE(2023,1,1)) * 270000.

This measure is dynamic and should meet your requirement for handling both slicer selections and the default case when nothing is selected. Let me know if you need further adjustments! 😊

Please mark this as solution if it helps. Appreciate Kudos.

[Anonymous · Accepted solution]

Hi @euro_sree ,

The method FarhanJeelani provided should be helpful.

Besides, you can also try the following DAX formula to calculate the max value based on slicer.

Maximum Target Turnover = 
VAR SelectedYears = VALUES('Table'[Year])
VAR StartDate = DATE(2023, 1, 1)
VAR TodayDate = TODAY()
RETURN
IF (
    ISFILTERED('Table'[Year]),
    CALCULATE(COUNTROWS('Table') * 270000),
    DATEDIFF(StartDate, TodayDate, DAY) * 270000
)

Best Regards,

Wisdom Wu

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

[FarhanJeelani · Accepted solution]

Hi @euro_sree ,

To achieve your requirement, you can create a DAX measure that dynamically calculates the maximum value based on the selected year(s) in the slicer. Here’s how to do it:

Steps:

1. Assume You Have a Date Table:

   • Make sure your data model includes a properly set up Date table, with a relationship to your fact table containing turnover data.

2. Create the Measure:

   • Use the following DAX code to create the measure:

   Maximum Target Turnover = 
   VAR SelectedYears = VALUES(DateTable[Year]) -- Get the selected years
   VAR TotalDays =
       IF(
           ISFILTERED(DateTable[Year]),
           SUMX(SelectedYears, 365), -- Calculate total days for selected years
           TODAY() - DATE(2023, 1, 1) -- Days from 01/01/2023 to today
       )
   RETURN
       TotalDays * 270000

3. Explanation:

   • VALUES(DateTable[Year]): Captures the selected years from the slicer.
   • SUMX(SelectedYears, 365): Calculates the total days for all selected years (365 days per year).
   • ISFILTERED(DateTable[Year]): Checks if the slicer has any filter applied; if not, it defaults to calculating the days from 01/01/2023 to TODAY().
   • 270000: The daily target turnover is multiplied by the total days.

4. Add the Measure to Your Visual:

   • Use the measure in a card or visual to display the calculated maximum target turnover.

5. Test the Functionality:

   • Select different years in the slicer and verify that the calculation changes accordingly.
   • If nothing is selected in the slicer, ensure the calculation uses (Today() - DATE(2023,1,1)) * 270000.

This measure is dynamic and should meet your requirement for handling both slicer selections and the default case when nothing is selected. Let me know if you need further adjustments! 😊

Please mark this as solution if it helps. Appreciate Kudos.

[euro_sree]

Hi Farhan

Thank you so much for the solution and appreciate the way its been explained.

I will try this today and update you.

Regards