topic Re: Monthly Progress in % as Gauge Target in DAX Commands and Tips

Monthly Progress in % as Gauge Target

yoa380 — Tue, 26 Mar 2024 16:30:43 GMT

Hello, I have in my Power BI report a gauge chart showing the production attainment. Let's say that today 26 march, attainment is 80%.

I would like to add a target on this gauge. A dynamic target that would show the month progression.

For today 26 march (I want the target to be 84% (as 26 march is 84% of whole march month having 31 days). We would visually see the attainment is under target.

If on my report, i select the previous month (2024 - february) of course I want to have 100% and if april selected, it would still be 0%.

What is the best way to do that ? i thought about an excel file for each year/month and a formula but it would mean to get inside the file everyday and update/save it for the formula to refresh. any other way with DAX ?

Re: Monthly Progress in % as Gauge Target

Anonymous — Tue, 26 Mar 2024 17:55:58 GMT

Hello,

I got this measure for you :

Target exercice for current month = DIVIDE( DAY(TODAY()),DAY(EOMONTH(TODAY(),0)) )

You can complete it with a IF statement that check if your selected month is before or after current month. If it's before, your target is 1, if it's after, your target is 0.

I can help you more if you give me a sample of your dataset without real value.

Re: Monthly Progress in % as Gauge Target

Anonymous — Wed, 27 Mar 2024 07:02:19 GMT

Hi yoa380 ,

Thanks for the reply from Anonymous , please allow me to provide my insight:

Please create a measure:

Month Progression % = VAR StartMonth = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) VAR EndMonth = eomonth ( tday (), 0 ) VAR DaysPassed = TODAY ()-StartMonth VAR TotalDays = EndMonth-StartMonth RETURN DIVIDE ( DaysPassed, TotalDays, 0 ) * 100

Put "Month Progression %" into the Target value of the Gauge.

The page result is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: Monthly Progress in % as Gauge Target

yoa380 — Wed, 27 Mar 2024 10:01:14 GMT

Hello, Thanks a lot for this measure. I have now succeeded to add it as target value. On my gauge it appears well. But if I change the month in filter on my page (for example February), it would still show 87% (I want 100%) and if I select April I want 0%. Any idea of how to add IF rules in the measure you created for me ?

I have attached a picture with my gauges and my filters (year / month) from a calendar table.

Re: Monthly Progress in % as Gauge Target

Anonymous — Fri, 29 Mar 2024 08:48:11 GMT

Hi yoa380 ,

Please try to create a Measure:

Measure % = VAR SelectedMonth = MONTH(SELECTEDVALUE('Table'[Date])) RETURN IF(SelectedMonth < MONTH(TODAY()), 1, IF(SelectedMonth > MONTH(TODAY()), 0, [Month Progression %]))

You should have achieved your needs now:

The pbix file has been attached.

If you have other questions, please contact me at any time.

Best Regards,
Yang
Community Support Team