wk vs last wk comparing

[nganbla]

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

[amitchandak · Accepted solution]

@nganbla 

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

[amitchandak · Accepted solution]

@nganbla 

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

[Greg_Deckler]

Maybe:

volumelastweek = CALCULATE([volumnethisweek],MAX([wkno])-1)

[nganbla]

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

[Greg_Deckler]

Sorry:

volumelastweek = CALCULATE([volumnethisweek],[wkno]=MAX([wkno])-1)

[nganbla]

dear Sir i used your both Dax, 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.

[Greg_Deckler]

OK, I actually tested this one:

Measure = CALCULATE([volumnthiswk],FILTER(ALL('Table'),[wkno]=MAX([wkno])-1))

[nganbla]

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

[dax]

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.

[nganbla]

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.

[dax · Accepted solution]

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.

[nganbla]

hi, i check again, 

it work for almost case, but some not correct , i am trying to explore data accuracy 

[nganbla]

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

[amitchandak]

@nganbla , Max will not work outside the filter clause. So use filter(table, table[Week] =max(table[Week]))

Change as per need