cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Post Patron

## How to get specific value of current month across all years?

I have a table with the following data:

The table  calculates the total forecasted amount for each month (all years combined so not for a seperate year).

I would like to have a measure that takes the value of the current month (so in this case May), but in such a way that even if I set a filter on current year only, that it still takes all the years together.

So now I would like to have the value 329, but the value should be consistent so that if I select a filter on a visual for example: is in this year that it still shows the combined value of all years together which is 329 and not filtered on this year.

I hope my explanation is clear. If not let me know and I'll try to explain it better.

1 ACCEPTED SOLUTION
Community Support

Hi @Niels_T ,

If you want to remove the filter of year in sum of Forecast, you can try ALL or ALLEXCEPT function.

Here I use ALLEXCEPT function, as below, sum of Forecast will be only filtered by month, not by year. If this month is may it will give you sum of may = 329.

``````Forecasted Amount =
VAR percentage =
DIVIDE (
SUM ( 'Credit Memo + Invoice'[Amount] ),
CALCULATE ( SUM ( 'Credit Memo + Invoice'[Amount] ), ALLSELECTED () )
)
RETURN
CALCULATE (
SUM ( 'Forecast'[Forecast] ),
ALLEXCEPT ( 'Forecast', 'Forecast'[Month] )
) * percentage``````

If this reply still couldn't help you solve your problem, please share a sample file with me and show me the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

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

5 REPLIES 5
Continued Contributor

Can you give an example of what your end result should look like?

Post Patron

The end result is used for a gauge:

In the gauge I have YTD amount and the total amount we have to reach at the end of this year.

The target value for the gauge should be 329 as it is May.

A seperate function is then needed because I use a filter on this gauge to take the forecasted amount for this year.

Continued Contributor

Try using "ALL" in the measure you will create to grab the value for May (or any other month). This will ignore the filter for this year.

Post Patron

Could you show me how this can be added to the measure?

``````Forecasted Amount = VAR percentage = Divide(SUM('Credit Memo + Invoice'[Amount]), calculate(SUM('Credit Memo + Invoice'[Amount]), allselected()) ) RETURN
SUM('Forecast'[Forecast])* percentage``````
Community Support

Hi @Niels_T ,

If you want to remove the filter of year in sum of Forecast, you can try ALL or ALLEXCEPT function.

Here I use ALLEXCEPT function, as below, sum of Forecast will be only filtered by month, not by year. If this month is may it will give you sum of may = 329.

``````Forecasted Amount =
VAR percentage =
DIVIDE (
SUM ( 'Credit Memo + Invoice'[Amount] ),
CALCULATE ( SUM ( 'Credit Memo + Invoice'[Amount] ), ALLSELECTED () )
)
RETURN
CALCULATE (
SUM ( 'Forecast'[Forecast] ),
ALLEXCEPT ( 'Forecast', 'Forecast'[Month] )
) * percentage``````

If this reply still couldn't help you solve your problem, please share a sample file with me and show me the result you want. This will make it easier for me to find the solution.

Best Regards,
Rico Zhou

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors