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

[Niels_T]

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.

[pborah]

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

[Niels_T]

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.

[pborah]

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.

[Niels_T]

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

[v-rzhou-msft · Accepted solution]

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.