Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
timmay
Advocate I
Advocate I

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.

 

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

mahoneypat
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.