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

DAX DAX Calculations
1

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
11677×684 87 KB

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
21755×549 79.7 KB

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 :slight_smile:

3
31660×527 62.4 KB

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

Appreciate any assistance.

Thanks,
Tim

2

Hi @Timmay,

Welcome to the Forum!

Quick question. Why have you created this P&L Table?

By the looks of it you have dragged fields from the P&L Table into the table visual on the page, instead of the measures you already have. It’s important to know that tables wheter created in Power Query or DAX are only evaluated at model refresh time. So the P&L Table won’t be re-calculated when a slicer selection on the page is changed.

I hope this is helpful.

1 Like
3

Hi Melissa,

I’m trying to create a P&L like the one on Sam’s showcase here:

4
41512×919 215 KB

It bugs me that I can’t see how the showcases are made even though i’ve paid for lifetime membership for all the courses.

I’ve tried hitting refresh after changing the slicers in my formula, still doesn’t work.

Tim

4

The showcase files are all downloadable for those with full course membership - through the Power BI Showcases course. I believe the screenshot you are showing is from the “Financial Reporting in Power Bi” module.

2 Likes
5

Thanks @Heather!

In addition @Timmay you can refer to this course in the portal, that covers related content.

I hope this is helpful.

2 Likes
6

that’s great, I didn’t realise these courses were there!

I’ve started the fin reporting w/pbi already thanks!!

2 Likes