Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

show previous week value

Hello Guys
I have created 2 measure opening value and closeing value week wise i want to slow opening value of new week should be closing value of previous week using dax 
please help
please refer below data set

WeekNOOpening inventorycloseing Inventory
week125
week257
week376
week464
week549

Closing inventory of week 1 is 5 then for week 2 opening inventory should be 5
please help
Thanks

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Solution Sage
    3 years ago

    Hi!

    Does you column [WeekNO] have entries as in your example, week1, week2 etc?

    This should work then. It depends on finding the WeekNO closest before to the current week and then determining "Closeing Inventory" for that week (to use as Opening for this week). 

    Opening Inventory = 
VAR ThisWeek = Table[WeekNO]
VAR PreviousWeek = 
CALCULATE(
    max('Table'[WeekNO]),
    FILTER(ALL('Table'), 'Table'[WeekNO] < ThisWeek
    )
)
return
CALCULATE(
    sum('Table'[Closeing Inventory]),
    FILTER(ALL('Table'), 'Table'[WeekNO] = PreviousWeek
    )
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Anonymous , refer below

       

      Work on Week Vs Week using Rank

      https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

       

      Example

      Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)	
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))