Hello everyone
I have two fact tables connected to a dimension table. Fact tables have a many-to-one relationship with Dimension.
I have the following fields displayed in a matrix in the Power BI visual.
I have a category that comes from the Dim table
Date, Weeks, A (measure), Cumulative Sum of A are fields and measures created using Fact Table 1.
Fact table 2 has summed values to make Measure B (Excel cell G above)
The cumulative sum of A - B gives the difference in measure [Cumulative sum of A] - [B]. (Cell H)
All these measures are returning correct values and well so far.
However, I face a challenge in calculating the calculation field (I) since its formula is MAX (Cumulative Sum of A) - [B] - Previous Result).
For example, for row 2, the result would be 0.
For row 3 the score would be 0-0.
For row 4 the result would be MAX(1147.83 -(0+0),0)= 1147.83
For row 5 the result would be MAX(1066.57 -(1147.83+0+0),0)= 0
So, basically the sum of the previous rows is subtracted from the current row. I tried to create in the same way as I have with the cumulative sum of measure A, but since B comes from fact table 2 which has no index, the same concept doesn't apply there.
Please let me know if anything is unclear.
