Forum Discussion
How to work Running Total with VALUES
hi lbendlin ,
I only partly followed your suggestion for Running Total of the differences in days between the current share date and the initial, first share date. I used the DATEDIFF function after setting a ‘First Share Date’ and a ‘Current ShareDate’.
Similarly, I set a ‘First Share Value’ and a ‘Last Share Value’. The difference between the two values gives me the Running Total in the filter context.
In other words, I kept the double relationship of the two date columns in the fact table with the Calendar Table, but I followed your suggestion of calculating the days as the difference between the two dates.
This is fine for now.
Below is a picture of the resultThank you
Hi gennaro_19,
Thank you for reaching out to the Microsoft fabric community forum. Thank you @ for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
First, thank you also for your attention to my case.
As I have already written in the comments to another post, DAX is beautiful because sometimes it offers more than one path to the goal. And certainly your solution could be a viable path.
But as one DAX guru also says, the devil is often in the details.
Your proposed solution completely bypasses the initial problem I posed: the two fact table relationships with the Calendar Table.
Your solution does not use the Calendar Table at all.
For the sake of simplification, I proposed a slim dataset, but the model in which I want to implement these solutions is more complex: in the real model we can have multiple payment/disbursement dates in the same month and duplicate enhancement dates.
In your example you have excluded from the fact table the duplicate payments that tie to different enhancement dates that if straddling the year, create mismatches.
Why does this happen? It is complicated to explain, intervene, and/or modify. I prefer not to touch the source of the data.
Completely neglecting the Calendar Table may be a solution, but if in my report I also want to analyze deposits/payments, your solution, in some cases does not align.
As you can see in the image I attach, the 18-12-2000 payment of 996.27 with your proposed solution is associated with the year 2001 because the measure “reads” the date of the valuation of the share and not the date of the payment/payment.
In other words, I cannot do without the Calendar Table in my model.
Thank you for your help and effort
gc