cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
timmay
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.

 

1.JPG

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.

 

2.JPG

 

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 😊

 

3.JPG

 

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
v-cazheng-msft
Community Support
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:

v-cazheng-msft_0-1614587149618.png

v-cazheng-msft_1-1614587149627.png

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.

 

View solution in original post

3 REPLIES 3
v-cazheng-msft
Community Support
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:

v-cazheng-msft_0-1614587149618.png

v-cazheng-msft_1-1614587149627.png

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.

 

View solution in original post

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

mahoneypat
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.