getting previous week value

[VladMatao]

Could someone tell me what is wrong with my measure?

measure =

var previousweek=

if(

MOD(SELECTEDVALUE('table'[yearweek]),100)=1,

SELECTEDVALUE('table'[yearweek])-49,

SELECTEDVALUE('table'[yearweek)-1

)

var prev week value =

CALCULATE(

SUM('table'[value]),

FILTER('table','table'[yearweek]=previousweek)

)

return prev week value

What I am expecting:

prev week value	yearweek	value	category	cat_type
30	202001	10	a	4g
10	202002	20	a	4g
40	201952	30	a	4g
...	201951	40	a	4g

I get a blank table (I have no filters or agregation on) and if I'm taking the measure out of the table they start to pop up.

[VladMatao]

@v-kelly-msft @amitchandak 

I think this one doesn't work because the "cat_type" and "category" will change, and if I want to show it for certain category types for example will give me the wrong value. I managed to do it with a column:

lastweekvalue = CALCULATE(SUM('table'[value]),FILTER('table',EARLIER('table'[category])='table'[category] && EARLIER('table'[KPI_type])='table'[KPI_type] && EARLIER('node'[cat_type])='table'[cat_type] && EARLIER('table'[year week])='table'[year week]+if(MOD('table'[year week],100)=52,49,1)))

 But I would still like to know if this is possible with a measure

[v-kelly-msft]

Hi @VladMatao ,

Can you show me your .pbix file if there's no confidential information inside?

 
Best Regards,
Kelly

[v-kelly-msft]

Hi  @VladMatao ,

You have to modify your measure as below:

Measure =
VAR previousweek =
    IF (
        MOD ( SELECTEDVALUE ( 'table'[yearweek] ), 100 ) = 1,
        SELECTEDVALUE ( 'Table'[yearweek] ) - 49,
        SELECTEDVALUE ( 'Table'[yearweek] ) - 1
    )
VAR prevweekvalue =
    CALCULATE (
        SUM ( 'table'[value] ),
        FILTER ( ALL ( 'Table' ), 'table'[yearweek] = previousweek )
    )
RETURN
    prevweekvalue

Finally you will see:

For the related .pbix file,pls click here.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

You have to use all

var prev week value =
CALCULATE(
SUM('table'[value]),
FILTER(all('table'),'table'[yearweek]=previousweek)
)
return prev week value

It is better to take week to a different table or preferably to date table. Create a rank.

Week rank = Rank(Week,Week[yearweek],,asc,dense)

var prev week value =

Var previousweekRank= maxx(Week,Week[Rank])-1
CALCULATE(
SUM('table'[value]),
FILTER(all('Week'),'Week'[Rank]=previousweekRank)
)
return prev week value

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin