Forum Discussion
How to work Running Total with VALUES
- 1 year ago
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 lbendlin ,
Sorry,
I had not authorised free access.
I fixed it.
Thanks for the tip
👍🙏
you will need to make both relationships inactive and then purely run off the raw tables. Unless you only want to show dates that have a payment?
You also forgot to mark the Calendar table as Date table.
Can you please explain why the Date Share value is a later date than the payment value? Not clear what your expected result is.
- gennaro_191 year ago
Helper I
hi lbendlin ,
Thank you for your help.
1) I have fixed the marking of the Calendar table as a date table.
2) I have seen that you have a different approach to solving the problem I have represented: you suggest making both relationships with the Calendar table inactive.
This solution also requires modifying the other measures I have created that require one of the two relationships to be active.
3) I thought, therefore, of duplicating the file.pbix dedicated exclusively to a Calendar table with inactive relationships with the Facts table. A file in which I could easily try out the measures you propose;
4) When I ask the Community for help, as in this case, I prepare a simplified dataset, with little data, so as to make the work easy for those who offer to help me.
The real dataset, the one in which I will then have to use the measures, is much more complex and is the monitoring of a Pension Investment Fund in which indeed, sometimes there are several payouts during the month, and the valuation of the share has its own official date, different from the payout date;
5) Business Intelligence with The DAX is a very versatile, very powerful tool that adapts to many contexts/databases. One of the features that I appreciate very much is the possibility of keeping changes to the source database to a minimum.
In other words, it is often possible to achieve a goal by following several paths, and I often prefer those that leave the starting database with its characteristics unchanged.6) The result I want to obtain is a Running Total of the days between two consecutive dates. For example, 33 days elapse between the valuation date 18-06-2001 and the previous valuation date 16-05-2001. Between the valuation date of 18-12-2000 and the previous valuation date of 16-11-2000 there are 32 days. Here I would like to obtain the Running Total of all these differences between dates at each individual date.
So at 18-12-2020 I get 32.
At 18-06-2001 I get 214 (which is the sum of all the differences up to that point (32+29+31+28+32+29+33 = 214).
Red box in the image.7) Similarly, I would like to obtain the Running Total for the differences between the current and previous valuation (Blue frame of the image.
Here is the link to the file with your approach
https://drive.google.com/file/d/1B1yY7-1F59Zo9mRQtd25ENzn41mlOIpX/view?usp=drive_link
I have made it free to access and edit the.pbix file, please try the solution directly in the file.
Thanks again.Best Regards
- lbendlin1 year ago
Super User
The result I want to obtain is a Running Total of the days between two consecutive dates.I don't fully understand what you are trying to achieve, but I don't think you need a running total. You only need to find the min and max date for the range you are interested in, regardless of how many valuation dates you have.
- gennaro_191 year ago
Helper I
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