Forum Discussion
Helper IICalculate previous week measure doesnt work
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.
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.
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.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.
2 Replies
- Mattiar
Resolver I
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.
Robin96Hey 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.