Matrix Table

[datagoblin77]

Hello, 

I am trying to buld a matrix that looks more or less like this. 

the Total occupied units is a measure that stems from the count of all the units that have 'Yes' in the Occupied column in the [Fact_units] table. The date are stored in theh [Dim_proj] tables. 
Projected move ins and move outs are als o measures in another table that counts move ins and move outs.
The total should be given by the occupied units + move ins - move outs. The next week, however should start with the total from the previous week.

In Excel it's easy because I can just point to a coordinate, which I can't do in DAX. 

I managed to do a running total but it doesn't work in a matrix.
So far, I tried with EARLIER, but I can't get it to work because there is no row context I can latch on to. 

I tried to create an if statement, but the problem is always the same: I can't get PBI to take the previous row's result as a basis for the next row starting point. Can someone help?

[v-yilong-msft]

Hi @datagoblin77 ,

I create a table as you mentioned.

Then I create three measures.

What can I do is calculating the total by creating a measure.

total1 = 'Fact_units'[total] + 'Fact_units'[ins] - 'Fact_units'[outs]

So if you can provide me more about your table or details, I will do a further study.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[datagoblin77]

Hello Ylong, 

Thanks for your reply. te measure you provided is good. The only problem is that the total has to have an IF statement that depends on a measure. The first weeek of the matrix is actually a COUNT of all the units that have a "yes" in the occupied column. 

If I were to do the same thing in any other programming language I would use a while loop. In pseudocode it should be something like:
total1=  [actual occupied units] + [ins] - [out]
total2= total1 + [ins] - [out]
i= 1
WHILE 

    counter < MAX(weekcount)
          IF weekcount = 1 
              total1
          else 
              total2 
               total2 =total2 + ins - out
       i ++
This should create a loop that iterates through the weeks. Unfortunately, PBI doesn't have while loops and the  variables are static. 

[v-yilong-msft]

Hi @datagoblin77 ,

It's true that there's no DAX code in Desktop that directly replicates the While loop statement as of now, but I think you can check out this topic: For and While Loops in DAX - Microsoft Fabric Community

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.