Help with Multiple Filters from User Selection Slicers

[PowerBI-Newbie]

Hi,

I have a calendar table which has columns Year and Period that I'm using as slicers:

I'm using these to filter out data from 6 data tables; these 6 tables also contain the columns Year and Period. When I previously had data for Year 19-20 only the figures were calculated correctly but when I've now added data for Year 20-21 I'm getting a summation of Years 19-20 and 20-21 instead of just the Year and Period selected.

I have the following data as an example:

The slicers are as follows:

When I select the Year and Period then the calculations are made based on this user selection. I have the following KPIs that I'm calculating:

Period calculation measure:

SIP Actual = (sum(SIP_Activities[Actual-LD]) + sum(SIP_Activities[Late Delivery]))

YTD calculation measure:

SIP Actual (YTD) =

CALCULATE (

SUMX('SIP_Activities', [SIP Actual]),

FILTER (

ALLEXCEPT ( 'Calendar_Table', 'Calendar_Table'[Year]),

'Calendar_Table'[Period] <= MAX ( 'Calendar_Table'[Period] )

)

)

I had many-many relationship and single direction (calendar table filtering the respective table) which worked fine until I had data for the new fiscal year. So now when I select Year 20-21 and Period 1 I'm getting 10 for Planned instead of 1, and consequently my YTD figure for the new financial year should 1 for Planned instead of 10.

Any help is greatly appreciated. 

[amitchandak]

@PowerBI-Newbie , In case you have date, prefer using time intelligence

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((Table[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR(Table[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd(Table[Date],-1,Year)),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-1,Year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[PowerBI-Newbie]

Hi @amitchandak ,

Thank you for your response.

Unfortunately we don't use date for this particular dashboard, it's only Fiscal Year and Period as per my data.

[Greg_Deckler]

If you could post the data as text could potentially recreate, otherwise nice job of following the principles in this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

However, I am thinking what you need to do is create two a new column in each of your tables that concatenates your year and period. Then create a new table Table = DISTINCT('Table1or2'[YearPeriod]).

Then use that in your slicer? Many to many relationships are generally bad.

[PowerBI-Newbie]

Hi @Greg_Deckler ,

Thank you for your quick response.

Here's the data as text:

Calendar:

Year	Period
19-20	1
19-20	2
19-20	3
19-20	4
19-20	5
19-20	6
19-20	7
19-20	8
19-20	9
19-20	10
19-20	11
19-20	12
19-20	13
20-21	1
20-21	2
20-21	3
20-21	4
20-21	5
20-21	6
20-21	7
20-21	8
20-21	9
20-21	10
20-21	11
20-21	12
20-21	13

Data:

Year	Period	Planned	Actual
19-20	1	9	9
19-20	2	15	15
19-20	3	16	17
19-20	4	14	15
19-20	5	12	10
19-20	6	13	13
19-20	7	10	13
19-20	8	11	13
19-20	9	9	9
19-20	10	12	13
19-20	11	9	9
19-20	12	10	12
19-20	13	9	3
20-21	1	1	1
20-21	2	2	2
20-21	3	3	3
20-21	4	4	4
20-21	5	5	5
20-21	6	6	6
20-21	7	7	7
20-21	8	8	8
20-21	9	9	9
20-21	10	10	10
20-21	11	11	11
20-21	12	12	12
20-21	13	13	13

I thought of your suggestion but the end-user needs the slicer to look like the screenshot from my original post plus I also have YTD calculations so not sure if that would have an impact. Excuse my silly questions but why does it work well for Year 19-20 but not for 20-21? For some reason I can't choose anything else other than many to many relationship. I also had both directional before but changing it to single direction doesn't seem to do anything.

[Greg_Deckler]

Will have to recreate to know. Give me some time. You could still have the slicer look like that, you would just create columns in your new table that split the year and period back out.

[Greg_Deckler]

@PowerBI-Newbie - I am missing some info to recreate this, Actual-LD and Late Delivery columns?

SIP Actual = (sum(SIP_Activities[Actual-LD]) + sum(SIP_Activities[Late Delivery]))

What are those in the data you presented?

[PowerBI-Newbie]

Apologies @Greg_Deckler , here's the complete dataset:

Year	Period	Planned	Actual-LD	Late Delivery
19-20	1	9	9
19-20	2	15	15
19-20	3	16	17
19-20	4	14	15
19-20	5	12	10
19-20	6	13	13
19-20	7	10	13
19-20	8	11	13
19-20	9	9	9
19-20	10	12	13
19-20	11	9	9
19-20	12	10	12
19-20	13	9	3
20-21	1	1	1
20-21	2	2	2
20-21	3	3	3
20-21	4	4	4
20-21	5	5	5
20-21	6	6	6
20-21	7	7	7
20-21	8	8	8
20-21	9	9	9
20-21	10	10	10
20-21	11	11	11
20-21	12	12	12
20-21	13	13	13

I must've missed Late Delivery out since it's an empty column at the moment but I guess in the future that might change, and didn't type the Actual-LD column properly.

[Greg_Deckler]

OK, yeah, just switching this from many to many to one to one fixes everything. I couldn't get the many to many work for either year.

See PBIX attached.

[PowerBI-Newbie]

Thank you very much @Greg_Deckler , that worked really well.

May I ask, how can I now filter the table such that when I select Period 2 for a particular fiscal year then it displays Periods 1-2 data for Planned or Actual-LD, and when I select Period 3 then it displays Periods 1-3 data?

[Greg_Deckler]

Oh, for that you need what I call a Complex Selector. I really have to post this out to the Quick Measures Gallery. It is basically the same concept as Inverse Selector.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290#M325

You create a measure that returns 1 if you want to show something and 0 otherwise and then filter on that measure. I am attaching an example.

This one is coming up all the time lately! I think 3 times today!!

[PowerBI-Newbie]

Thanks for your response @Greg_Deckler .

I had a look at the link and it mentions something about making sure there are no relationships but my slicers Period (1-13) and Fiscal Year (19-20, 20-21, etc.) have relationships with other tables. I also had a look at the file you attached and I was really confused (I'm noobie in Power BI).

So how do I tell it to display all Actual figures from Periods 1 to 3 if the user selects Period 3? I'll attached the pbix file once I remove all sensitive information so that you can see what I mean.

[Greg_Deckler]

All I can really say is that you are going to need to use ALL or ALLSELECTED somewhere to get rid of the filter from your slicer. Can work with relationships between things but generally makes it harder.

[PowerBI-Newbie]

Hi @Greg_Deckler ,

I've created the .pbix file to attach but unable to do so for some reason.

I'm trying what you said but it's not working for some reason.

[Greg_Deckler]

You will need to share it using OneDrive or Box or some other file sharing service and post link here.

@PowerBI-Newbie

[PowerBI-Newbie]

@Greg_Deckler,

Here's the link:

https://1drv.ms/u/s!AumDlNixB1k60FjdaJTdj8jKO31n?e=IqPxEj 

[Greg_Deckler]

OK, so regarding your request @PowerBI-Newbie - so am I missing something here or is:

May I ask, how can I now filter the table such that when I select Period 2 for a particular fiscal year then it displays Periods 1-2 data for Planned or Actual-LD, and when I select Period 3 then it displays Periods 1-3 data?

Soooo...isn't that just your YTD measure?

[PowerBI-Newbie]

@Greg_Deckler , the YTD measure is the cumulative sum up to and including the Period that the user has selected.

What I'm after is the figures of the Periods 1 to User Selection to be displayed as a line chart (under Trend in the visuals). So if Period 3 is selected then I'll see 3 points on the line chart.

[Greg_Deckler · Accepted solution]

A bit tricky, but a disconnected table trick and this measure later and I think I got it. PBIX is attached:

SIP Actual Line Chart = 
    VAR __Year = MAX('SIP_Activities'[Year])
    VAR __Period = MAX('SIP_Activities'[Period]) + 0
    VAR __LinePeriod = MAX('Periods'[Period]) + 0
    VAR __Table = FILTER(ALL(SIP_Activities),[Year] = __Year && [Period]+0 <= __Period)
    VAR __Table1 = FILTER(__Table,[Period]+0 = __LinePeriod)
RETURN
    SUMX(__Table1,[Actual-LD]) + SUMX(__Table1,[Late Delivery])

[PowerBI-Newbie]

Than you very much @Greg_Deckler . I copied and pasted your Measure into my dashboard and for some strange reason I get a VAR error message on my work Power BI Desktop (see error message):

In fact, I can't open the file as it's apparently a newer version. Having said that, I can open it on my personal laptop without any issues and can see your solution without any VAR error message. I checked the work version number and it's 2.73.5586.1101 (September 2019) while ony my personal laptop it's 2.80.5803.1061 (April 2020), could this be the issue? Is there a workaround?

[Greg_Deckler]

You are missing a paren ) @PowerBI-Newbie 

VAR __LinePeriod = MAX('Periods'[Period]) + 0

You have:

VAR __LinePeriod = MAX('Periods'[Period] + 0