[Single number card] Create visual filter where year=current year

[wech747]

Hi,

i am struggling with creating a dynamic filter on a single number card, which I want to show the sum of a value of the current year. So far, I've just ticked the appriopriate year in the visual filter 'year', but I want to make it dynamic.

I've already created a DAX formular to automatically get the current year, but how can I use it as a dynamic visual filter?

I would be very grateful about some help.

Kind regards

[Back2Basics]

Could you simply include a slicer for the year field?

If you change the orientation to 'Horizontal' you get a nice simple selection tool. Then you can select the year you are insterested in and if you want more than one year just hold Crtl when selecting.

[wech747]

Hi ceebu,

thanks for the advice. But unfortunately, in the available spreadsheet the "top" number isn't "2017", as there are also forecasts in there. So, the "Top N filter" doesn't fit in this case.

Hi Back2Basics,

thanks for your advice. You are right, there is this described option, but the thing is that I have to create 4 single number cards from totally different spredsheets without any relation. As a result I would have to create 1 "year"-slider for each number card - which isn't that beautiful in my opinion.

Isn't there any option to solve this use case in a more 'elegant' way?

[wech747 · Accepted solution]

...meanwhile I've got a solution for this case:

I just created a measure and integrated the filter argument in there:

sum of price in current year = CALCULATE(AVERAGE(Tabelle12[price]);Tabelle12[year]=YEAR(TODAY()))

Thanks for your help anyway!

[ceebu]

When you mean, dynamic - what other visual is driving the context for this card ?

eg: If you click the bar for "year" in another visual, then the corresponding sum should be displayed in the card - Is that how you are building this ?

If possible, please share a screenshot

[wech747]

Hi ceebu,

you are right, a screenshot might do a good job in this case:

I want to create a filter, which automatically selects the current year, instead of manually clicking it as shown in the screenshot.
Can you help me with that?

[ceebu]

You should be able to use the TopN filter to select the values dynamically.

http://www.cloudfronts.com/power-bi-new-updates-date-slicer-top-n-filter-and-data-label-improvements...

If your date column is formatted as date type, then you can select "Latest date" option to show current year.

[wech747]

Hi ceebu,

thank you very much for your answer.

Unfortunately column isn't formatted as date type, it hast just the data type 'number'.
When using the TopN filter as described, I unfortunately don't receive the correct numbers.

Is there any other possibility to solve this problem?

Kind regards

[ceebu]

When formatted a numbers, do you see the option "Top" under "Show Items" ? You could probably try Top "1" and in the "ByValue" field, use the date column.

[ceebu]

If i understand you right, you want to be able to filter by the current year without manually selecting the year everytime it changes - Right ?

If yes, you should be able to use the TopN filtering method.

See this link : http://www.cloudfronts.com/power-bi-new-updates-date-slicer-top-n-filter-and-data-label-improvements...

Is your Date column formatted as Date type ? In that case you should be able to filter for the latest date - Which will give you current year.