I am designing a report (query) in Query designer and have been struggling with the following problem for a week. Any help is appreciated.
So, I've got a report with a dozen of characteristics in Rows. Two of them are date characteristics. What I need is to display the difference in days between these two dates (as a CKF/formula) and, upon computing the date difference and based on the output value, create another Calculated Key Figure that is dependent on the Date Diff CKF. To calculate the date difference, I have created formula variables with replacement path from both date characteristics, so the Date Diff CKF works okay (one variable minus the other). The issue is that when I remove either of the characteristics from the Rows section in Excel, the Date Diff CKF ceases to work properly and displays only 'X', which obviously prevents me from using this CKF in my further calculations. The question is: is there a solution that would calculate the date difference if any of the date characteristics isn't in Rows?
Thank you in advance,