Calculate previous week measure doesnt work

[Robin96]

Hey all,

i have been having trouble with this dax for quite some time now.. and i really need to figure this out.

1. on the first week i want cash balance.
IF(SELECTEDVALUE('date'[year_week]) = calculate(MIN('date'[year_week]), ALLSELECTED('Date'[Week])),[Cash balance],0)5

2. on the 2nd week i want net cashflow same week + previous week cash balance.

IF(SELECTEDVALUE('date'[year_week]) = calculate(MIN('date'[year_week])+1, ALLSELECTED('Date'[Week])),[net cash + balance],0)

3. on the 3rd week i want net cashflow same week + the 2nd week value.
Then i want to use the same logic as the 3rd week measure until the end.

This spreadsheet is my desired output:

This is my model:

This is how the table looks now:

i have figured out how to  calculate the row for week 1 and week 2. But not week 3.
On week 3 i want to use the net cashflow same week + previous week "Week 2 (Correct)" measure.

[Mattiar · Accepted solution]

Hi Robin,

first of all, i don't understand why you have that kind of relationships between tables. 

You should have a star schema  with one directional among dimensions and facts.

An advice i can give u, is try to use new calculation visual with its functions previous row.

Otherwise you would have to construct a more complicated dax function where you could select the week you want on a TOPN function.
For example, inside a variable you make yourself a SUMMARIZE in which you put the weeks in order. And if necessary you call up the week number with a SELECTEDVALUE.

[Mattiar · Accepted solution]

Hi Robin,

first of all, i don't understand why you have that kind of relationships between tables. 

You should have a star schema  with one directional among dimensions and facts.

An advice i can give u, is try to use new calculation visual with its functions previous row.

Otherwise you would have to construct a more complicated dax function where you could select the week you want on a TOPN function.
For example, inside a variable you make yourself a SUMMARIZE in which you put the weeks in order. And if necessary you call up the week number with a SELECTEDVALUE.

[Robin96 · Accepted solution]

Hey Mattiar,

Thanks for you response! The reason i have this kind of relationship is because this is just a "testing" report i made to understand and test these calculations. my actual report have a different kind of relation between tables.

i will check out the new calculation visual and see if that will work any better, thanks!

I would be happy if there is someone else here with an solution here also.