Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
hi all,
need some advice here on the chart filtering.
i have 3 measure calculated using the filtering
next i need to use the above measure to do further % change calculation.
however i got blank, i think it got to do with filtering but doesnt get the correct value.
Can anyone advice? Thank you.
Solved! Go to Solution.
Please check the file.
https://www.dropbox.com/s/3svdbxr8akcfctc/filter.pbix?dl=0
Issues are with the data type.
Hi @Anonymous ,
You can try this measure.
Last 12 Mths New = VAR _Curr_start = MAXX ( DATA1, DATEADD ( STARTOFMONTH ( DATA1[Date1] ), -11, MONTH ) ) VAR _Curr_END = MAX ( DATA1[Date1] ) RETURN SUMX ( FILTER ( ALL ( DATA1 ), DATA1[Date1] >= _Curr_start && DATA1[Date1] <= _Curr_END && DATA1[Product] = SELECTEDVALUE ( DATA1[Product] ) ), DATA1[Value] )
Here is the result.
Please check the file.
https://www.dropbox.com/s/3svdbxr8akcfctc/filter.pbix?dl=0
Issues are with the data type.
The last 12-months table cal has issues.
You were able to see data because you have stopped time filter on that visual.
Please see if this calculation suffices your needs.
Last 12 Mths New = VAR _Curr_start = Maxx(dates,DATEADD(STARTOFMONTH(Dates[Date]),-11,MONTH)) VAR _Curr_END = Max(Dates[Date]) Return CALCULATE(sum(DATA1[Value]),ALL(DATA1),DATA1[Date]>=_Curr_start && DATA1[Date]<=_Curr_END)
https://www.dropbox.com/s/3svdbxr8akcfctc/filter.pbix?dl=0
Calc of 12 month is correct. Currently, the calendar end date is 31-Dec-2019. So max of that has been taken. Now there two options.
1. when you have a slicer, it will respond to that. I checked it
2. Or in the max, you can use the max of sales date.
@amitchandak hi,the value of last12 mths is based on all products?
i tried using adding product filter but the salechange value doesnt change and the last12 mths sale value wrong.
Remove all(DATA1) from the formula. I added that when I did not realize that edit interaction casing issue.
Last 12 Mths New = VAR _Curr_start = Maxx(dates,DATEADD(STARTOFMONTH(Dates[Date]),-11,MONTH)) VAR _Curr_END = Max(Dates[Date]) Return CALCULATE(sum(DATA1[Value]),DATA1[Date]>=_Curr_start && DATA1[Date]<=_Curr_END)
@amitchandak
I not sure where i gone wrong, but i cant get the value for last12mth correctly.
I am trying to get product A, Jan to Dec 2018 sale total = 824
Can you advice? Thanks so much.
Please check the column you have taken "date" from the Data1 filter. I think the date of DATA1 is not date. Check you table in model view.
Date1 is in date format from Data1. Also, the relation between Data1 and dates is one-directional. So you have to date from the dates table.
That is giving the correct answer.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |