Forum Discussion

NovaBI's avatar
NovaBI
Icon for Post Patron rankPost Patron
6 years ago
Solved

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

 

 

Need Help

2 Replies