cancel
Showing results for
Did you mean:
Frequent Visitor

## Creating a table with slicers to select financial year & period via a measure

Hello,

I’m trying to create a table that updates as different financial years and periods are selected from a slicer.

The screen shot should really say it all.

My filter in the calculate formula is filtering the [period select] measure. The [period select] measure is

Period Select =
MAX( Dates[Period] )

The dates [fyear] is hardcoded as it contains the desired output that I would like from a similar measure selection for financial year I have hardcoded this for illustrative purposes.

If I hardcode the [dates period] as 4 I get the answer I am after. But I would like this to be responsive to the slicer selections on the page.

So, the issue that I’m having is that, even though the measure [period select] changes as I make slicer selections the output of the measure [sales of fyear & period select] does not change as I change the selections of the slicer.

However if I change the dates[period] to be a hardcoded number such as 4, then the output of [sales of fyear & period select] is as desired.

If the measure [period select] is = to 4 then why the need to hardcode a period?

This 2nd screen shot contains the desired output, as you can see the 4 is hardcoded, so the sales number has changed.

I should also add, this is how the sales number is shown, through a created table, if you have ever tried to create a financial table in power bi you will understand what my end game is 😊

I hope this is all that is needed to solve this issue, please let me know any questions.

Appreciate any assistance.

Thanks,
Tim

1 ACCEPTED SOLUTION
Community Support

Hi, @timmay

You can consider using SELECTEDVALUE function to get selected value in Slicer and use it to do calculation.

Sales of FYear&Period Select =

CALCULATE (

SUM ( 'Table'[sales] ),

FILTER (

'calendar',

'calendar'[FYear] = SELECTEDVALUE ( 'calendar'[FYear] )

&& 'calendar'[Period] = SELECTEDVALUE ( 'calendar'[Period] )

)

)

The result looks like this:

Here is the pbix.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi, @timmay

You can consider using SELECTEDVALUE function to get selected value in Slicer and use it to do calculation.

Sales of FYear&Period Select =

CALCULATE (

SUM ( 'Table'[sales] ),

FILTER (

'calendar',

'calendar'[FYear] = SELECTEDVALUE ( 'calendar'[FYear] )

&& 'calendar'[Period] = SELECTEDVALUE ( 'calendar'[Period] )

)

)

The result looks like this:

Here is the pbix.

Best Regards,

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

and everyone told me it can't be done! well done 😄

Super User IV

FYI that DAX Tables and Columns you create will not respond to slicers; they only update upon refresh.  Also, the slicers on FYear and Period in your Dates table should automatically filter your fact table to get your desired result without having to add the FILTER clause to your expression.

If you can share a link to a pbix file with mock data, a specific solution will likely be provided by the community.

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Announcements