13 Week Matrix overlapping 2 years

[Anonymous]

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.

[v-yuta-msft]

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

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

@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.