Firstly, let me point out to a fact that what you’re trying to achieve is not feasible because you’ve a bi-directional filter on in your data model which is absolutely not recommended and just by replacing the field from “Table[Date]” to “Table[End Of Quarter]” won’t provide you the results for that you need to replace the field into the visual itself.
When I removed the bi-directional filters from your model it provided the correct results for “Cumulative Values - Inactive Relationship”.
Below are the screenshots provided for the reference step-by-step:
- Bi-directional Filters in your data model -
- Due to Bi-directional Filters your results were incorrect -
- Remove the Bi-directional Filters from the data model -
- Correct results were obtained after removing the Bi-directional filters. Identical to my results -
On top of all these you hadn’t marked your date field as a “Date Table” which led to ambiguous results.
Also when you posted your second post it was very clear that you wanted the 3 data points only and therefore, I changed the field into the visual from “Calendar[Date]” to “Calendar[End of Quarter]”. Just by placing the field into the “Formula” itself one cannot derive that many data points into the visual. We need to place that particular field into the visual in order to derive/or have that many data points in it.
I hope now I’ve made my points clear that why I’ve replaced the field into the visual to have 3 data points. And therefore for different data points different fields need to be inserted as I’ve done in my case.
Lastly, before I sign off from this post. I would also like to highlight that as you can see that in the table provided above that once you change the filter propogations into the data model it repeats the total from the previous quarter. Now, if you don’t want to have that kind of results into your table you can write the formula as provided below -
Sum Values - Inactive Relationship - Harsh 1 =
CALCULATE( SUM( 'Table'[Value] ) ,
USERELATIONSHIP( 'Table'[Date] , 'Calendar'[Date] ) )
Cumulative Values - Inactive Relationship - Harsh 2 =
VAR CumulativeValues =
CALCULATE( [Sum Values - Inactive Relationship - Harsh 1] ,
FILTER( ALLSELECTED( 'Calendar'[Date] ) ,
'Calendar'[Date] <= MAX( 'Calendar'[Date] ) ) )
IF( ISBLANK( [Sum Values - Inactive Relationship - Harsh 1] ) ,
And then the result will be like this as per the screenshot provided below -
Important Note: Also I’ve observed in your formulas that you’ve used “USERELATIONSHIP” function multiple times. I would like to highlight that you just need to use “USERELATIONSHIP” or “TREATAS” functions only once while writing the primary formulas and from there on the measure branching technique will automatically take care off of the remaining secondary formulas that you write.
Hoping that this explanation makes it clear from my side and helps you to achieve the results what you’re looking for.
Also attaching the final working of the PBIX file for the reference.
Thanks and Warm Regards,
InactiveRelationship - Harsh Final Version.pbix (76.2 KB)