Forum Discussion

PowerBI-Newbie's avatar
PowerBI-Newbie
Icon for Helper IV rankHelper IV
6 years ago
Solved

Help with Multiple Filters from User Selection Slicers

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. 
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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])

23 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      PowerBI-Newbie
      Icon for Helper IV rankHelper IV
      6 years ago

      Hi Greg_Deckler ,

       

      Thank you for your quick response.

       

      Here's the data as text:

      Calendar:

      YearPeriod
      19-201
      19-202
      19-203
      19-204
      19-205
      19-206
      19-207
      19-208
      19-209
      19-2010
      19-2011
      19-2012
      19-2013
      20-211
      20-212
      20-213
      20-214
      20-215
      20-216
      20-217
      20-218
      20-219
      20-2110
      20-2111
      20-2112
      20-2113

       

       

      Data:

       

      YearPeriodPlannedActual
      19-20199
      19-2021515
      19-2031617
      19-2041415
      19-2051210
      19-2061313
      19-2071013
      19-2081113
      19-20999
      19-20101213
      19-201199
      19-20121012
      19-201393
      20-21111
      20-21222
      20-21333
      20-21444
      20-21555
      20-21666
      20-21777
      20-21888
      20-21999
      20-21101010
      20-21111111
      20-21121212
      20-21131313

       

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago
        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.
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      PowerBI-Newbie
      Icon for Helper IV rankHelper IV
      6 years ago

      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.