cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Best practice multiple filters, with measures

Hi peeps,

 

i try to describe my issue as good and as clean as possible:

 

I imported a dataset from SAP via Import 2.0.

 

Therefore i have an "amount" column from which the current numbers are pulled when specific items are pulled in (lets say sth. like Storecodes/Countries,....).

 

Additionally, we have 2 years to take a look on (Current Year = CY, Last Year = LY). Plus, there is a tag thats needs to be pulled in for the fiscal year. Meaning the values for LY needs to be defined by FiscalYear20, the ones for CY by FiscalYear21. Furthermore, a "Week" per year is defined that needs to be connected to the right fiscal year. Example: 2001 (first week of 2019!) needs to be calculated by amount, FiscalYear(19) and so on (around 4 items).

 

I defined different measure for visualisations that work perfectly. Now i want to calculate a YoY performance and i don't know the best way of doing that. I tried with Calculate(), but i know it does not work with measures. So I tried calculate and filter, but suddenly, i was not sure and was thinking if it is better to define 2 seperate measures and calculate them afterwards as a variance/difference.

 

CY Testcode CY = CALCULATE
(SUM('Analysis (PowerBI)'[Amount]),'Analysis (PowerBI)'[Data Type] = "Adjusted",'Analysis(PowerBI)'[Year_SF]=21)
 
this is one example of how i did a measure for this year. I used then filters for the visuals directly as users want to see them.
 
But for the variance, i want to create sth. in the background so noone has to fiddle around in the frontpage.
 
I tried sth. like XYZ = Calculate(Sum(table1)[Amount]), Filter[table1,table1[CY Testcode]),Filter2(table1,table1[PY Testcode]))
-> this code is not perfectly right, just want to show what i was thinking of. Additionally, i wanted to create sth like -> if the week version is >= 2101 then just take FiscalYear>20...tried it with the filter + &&, could not find the right way somehow :=)
 
Sorry for my partly confusing description, I am in a hurry as I am on the road. If needed, I can provide better codes/hands on examples and descriptions tomorrow or monday. Sorry upfront for that. But perhaps we can achieve some progress until then.
 
Thanks and regards

 

 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @NovaBI ,

Please check if the following documentations can help you. If no, please provide some sample data and your expected result, later will provide the suitable formula for your scenario.

POWER BI – YEAR OVER YEAR (YOY) REPORTS USING SAMEPERIODLASTYEAR

Complex Filter Conditions In Power BI Reports Using Visual-Level Filters And Measures

Favorite Quick & Easy DAX: Multiple FILTER Criteria

Best Regards

Rena

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

View solution in original post

2 REPLIES 2
Helper III
Helper III

forgot the minus here:

 

I tried sth. like XYZ =

Calculate(Sum(table1)[Amount]), Filter[table1,table1[CY Testcode])

    %

       Calculate(Sum(table1)[Amount]), Filter[table1,table1[PY Testcode])

Community Support
Community Support

Hi @NovaBI ,

Please check if the following documentations can help you. If no, please provide some sample data and your expected result, later will provide the suitable formula for your scenario.

POWER BI – YEAR OVER YEAR (YOY) REPORTS USING SAMEPERIODLASTYEAR

Complex Filter Conditions In Power BI Reports Using Visual-Level Filters And Measures

Favorite Quick & Easy DAX: Multiple FILTER Criteria

Best Regards

Rena

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

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors