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
i have data as below
name: is column
wkno is column
volumethiswk is measure (working well)
now, i wanna make another measure for volumelastweek then can show data for last week then I can create a new measure for difference number week vs week.
pls give me a hand
name | wkno | volumethiswk | volumelastweek |
a | 1 | 30 | 0 |
a | 2 | 20 | 30 |
a | 3 | 40 | 20 |
Solved! Go to Solution.
Check my file how I used Week Rank/Week number to get current vs prior week
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
Better you move week to separate table.
This week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Week'),'Week'[Week]=max('Week'[Week])))
last week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Week'),'Week'[Week]=max('Week'[Week])-1))
Prefer the rank solution. As that will work Multi Year
Hi @nganbla ,
You could try measure like below to re-define the grand total to see whether it work or not.
Measure 2 = IF(HASONEVALUE(T2[name]),[Measure],SUMX(T2,[Measure]))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check my file how I used Week Rank/Week number to get current vs prior week
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
Better you move week to separate table.
This week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Week'),'Week'[Week]=max('Week'[Week])))
last week Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Week'),'Week'[Week]=max('Week'[Week])-1))
Prefer the rank solution. As that will work Multi Year
Maybe:
volumelastweek = CALCULATE([volumnethisweek],MAX([wkno])-1)
i use your code but it show
A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.
but not use MAX for any others one
I tried all about but there is error with Max and / or also have empty result.
a draw again the details here, hope you can help me more
many thanks
Hi @nganbla ,
You could try below expression(if you want to show 0 )
Measure = CALCULATE(SUM(T2[volume]), FILTER(ALLEXCEPT(T2,T2[name]), T2[wkno]=MIN(T2[wkno])-1))+0
T2:
namewknovolume
a | 1 | 2 |
a | 2 | 3 |
a | 3 | 4 |
a | 4 | 5 |
a | 5 | 6 |
b | 1 | 3 |
b | 2 | 4 |
b | 3 | 5 |
b | 4 | 6 |
b | 5 | 7 |
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
My Dax for Measure TM_Total_TEU_Lastwk as below
TM_total_TEU_lastwk = CALCULATE(
SUM(ia_cnts[Tteu]),
FILTER(ALLEXCEPT
(ia_cnts,ia_document[Booking Shipper]), ia_cnts[C_wkno]=min(ia_cnts[C_wkno])-1)) + 0
i have visual for top TEN shipper by TEU and by date slidcer
i run your dax to try to make last week volume and also to click for 1 account to check easier
it seem correct for almost case, but the visual table we have total wrong.
Hi @nganbla ,
You could try measure like below to re-define the grand total to see whether it work or not.
Measure 2 = IF(HASONEVALUE(T2[name]),[Measure],SUMX(T2,[Measure]))
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi, i check again,
it work for almost case, but some not correct , i am trying to explore data accuracy
i tried, it show result but not correct one
i am using measure like
on this dashboard, the bkgparty is the one to select, then it will show volume by wk
any recomand for me
thank you
@nganbla , Max will not work outside the filter clause. So use filter(table, table[Week] =max(table[Week]))
Change as per need
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |