Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

13 Week Matrix overlapping 2 years

 

Hi, I'm relatively new to PowerBI and have been struggling with formatting data that is from different sections of two fiscal years. In this specific example I am trying to create a 13 Week analysis with 3 weeks from 2019 and remaining from 2018. Thanks in advance for any help!

 

Selecting the Fiscal Weeks is easy however only showing data from one year for them is a pain. It seems like I can only filter for one year at a time in a single matrix if I don't want multiple years worth of data combined into each week. Screenshot below is a demonstration of what I'm working with. 

 

I would like weeks 1-3 to only contain 2019 data and weeks 43-52 to show only 2018. Once that is completed I am then going to do the exact same matrix for previous year same period.

 

 

 

Need Help

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    Anonymous，

     

    So your issue is currently values from different years have been combined in week 1~3 and 43~52 and you want to seperate them, right? To be general,  I would suggest you to create a calculate column using DAX like pattern below:

     

    Flag =
IF (
    ( Table[FiscalWeek] IN { 1, 2, 3 }
        && Table[Year] = 2019 )
        || ( Table[FiscalWeek]
        IN { 4, 5, 6, 7, 8, 9, 10, 11, 12 }
        && Table[Year] = 2018 ),
    1,
    0
)

    Then create a slicer visual based on Flag column, select 1 to filter the table visual.

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-yuta-msft I apologize I don't have all that much experience so I have a few follow up questions. In the formula you've posted I need to include the actual table name from my data sets where I see "Table" as well as include exact field names such as Fiscal Year instead of year?

       

      I was able to follow your instuctions however when I filter it removes every week not 1-3 from my matrix. Screenshot is below. 

       

       

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        7 years ago

        Anonymous,

         



        v-yuta-msft I apologize I don't have all that much experience so I have a few follow up questions. In the formula you've posted I need to include the actual table name from my data sets where I see "Table" as well as include exact field names such as Fiscal Year instead of year?

         

        I was able to follow your instuctions however when I filter it removes every week not 1-3 from my matrix. Screenshot is below.

         

        Yes, the formula I have posted is based on the actual table. If the matrix contains columns from other dimensional tables, then the formula may not work. You should use expressions like IN VALUES(Dimensional_Table[Slicer_Column]) instead. Actually which kind of formula should be used is based off your spefic senario and data model.

         

        Community Support Team _ Jimmy Tao

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.