Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
NovaBI
Helper V
Helper V

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

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
NovaBI
Helper V
Helper V

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

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.