Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Niels_T
Post Patron
Post Patron

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

I have a table with the following data:

Niels_T_1-1652277530498.png

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

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.

 

View solution in original post

5 REPLIES 5
pborah
Continued Contributor
Continued Contributor

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

The end result is used for a gauge:

Niels_T_0-1652279391801.png

 

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
Continued Contributor
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.

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

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.

 

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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