Forum Discussion
Helper IHow to work Running Total with VALUES
I have a table for accounting for deposits/payments made and the values of the quotas when they are published.
A calendar table completes my data set.
I have created an active relation with the date of the payments
and a second inactive relation with the date when the quotas are published.
Finally, step by step, I created a number of measures that will help me follow the development of the value of the shares over time (the last value of the share, the previous value, the previous valuation date, the difference in value between the current and previous share value, the days between the various valuation dates).
I was now in the process of creating the measures to detect the Running Total of the differences between the values of the shares and the days between one date and the next.
I had to stop myself in the face of difficulties because I need to use VALUES to have the unique dates of valuation of the quotas while for the Running Total I need to use ALL which also shows me the duplicates of the valuation dates when, for example, there is more than one purchase/payment in a month.
I would like to obtain this result
I enclose below the link to the pbix file
https://drive.google.com/file/d/1Aw73Thv-iMAdaSAX5p0ipnMw5BFUcVbL/view?usp=drive_link
Thanks to all
gennaro
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
13 Replies
- lbendlin
Super User
link requires access, please check.
gennaro_19hi lbendlin ,
Sorry,
I had not authorised free access.
I fixed it.
Thanks for the tip
👍🙏- lbendlin
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.
- DataNinja777
Hi gennaro_19 ,
You're trying to compute a running total over the unique dates of share valuation (Date Share Value), but you're encountering problems because using ALL brings in duplicate valuation dates due to multiple payments per month. To overcome this, you can create a running total using SUMX over a virtual table that returns only distinct valuation dates using the VALUES function, while still allowing accumulation by using FILTER and ALL.
Here's the DAX formula to calculate the running total of the share value difference:
RT_Diff_Share_Value = VAR CurrentDate = SELECTEDVALUE('DataBase'[Date Share Value]) RETURN CALCULATE( SUMX( VALUES('DataBase'[Date Share Value]), [Diff Share Value] ), FILTER( ALL('DataBase'[Date Share Value]), 'DataBase'[Date Share Value] <= CurrentDate ) )Similarly, to calculate the running total of the day difference between each valuation, you can use the following measure:
RT_Diff_Days = VAR CurrentDate = SELECTEDVALUE('DataBase'[Date Share Value]) RETURN CALCULATE( SUMX( VALUES('DataBase'[Date Share Value]), [Diff Days] ), FILTER( ALL('DataBase'[Date Share Value]), 'DataBase'[Date Share Value] <= CurrentDate ) )These measures ensure that each valuation date contributes only once to the running total, even if there are multiple payments on the same date. If your visual is using fields from the Calendar table and not directly from DataBase, and the relationship with Date Share Value is inactive, you can include USERELATIONSHIP('Calendar'[Date], 'DataBase'[Date Share Value]) inside the CALCULATE function to activate that relationship temporarily.
Best regards,
- gennaro_19
Hi DataNinja777 ,
First of all, thank you for your attention to my problem.
As you can see your solution does not work because it only values the results at 18-12-2000, for both of the two proposed measures 😥😥- DataNinja777
Hi gennaro_19 ,
Thank you for your feedback. The issue in the original running total measure is that VALUES('DataBase'[Date Share Value]) was used without properly filtering it to include only dates less than or equal to the current row context. This causes the measure to evaluate only for a single date—specifically the last one in the model—while all other rows return blank. To correct this, the virtual table inside SUMX needs to be filtered appropriately. The corrected version uses MAX('DataBase'[Date Share Value]) to capture the current row’s date in a matrix visual and applies this as a filter condition to the set of distinct valuation dates. Here's the corrected version of the measure for share value differences:
RT_Diff_Share_Value_Corrected := VAR CurrentDate = MAX('DataBase'[Date Share Value]) RETURN CALCULATE( SUMX( FILTER( VALUES('DataBase'[Date Share Value]), 'DataBase'[Date Share Value] <= CurrentDate ), [Diff Share Value] ) )And the equivalent measure for cumulative days difference is:
RT_Diff_Days_Corrected := VAR CurrentDate = MAX('DataBase'[Date Share Value]) RETURN CALCULATE( SUMX( FILTER( VALUES('DataBase'[Date Share Value]), 'DataBase'[Date Share Value] <= CurrentDate ), [Diff Days] ) )These measures ensure that the accumulation includes only distinct valuation dates up to and including the current one, allowing the running total to increment row by row in the matrix as expected.
Best regards,
v-kpoloju-msftCommunity Support
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.- gennaro_19
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
