How to work Running Total with VALUES

[gennaro_19]

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

[v-kpoloju-msft]

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]

hi @v-kpoloju-msft 

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

[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,

[gennaro_19]

Hi @DataNinja777 ,

Thank you again for the help you are providing.
I have supplemented your measures with the activation of the second relationship between the calendar dates and the dates of the odds valuation.
But as you can see the proposed measures still do not work, still do not return a Running Total and I am reliving the same frustrations of my many attempts before asking the Community for help.

I am a DAX neophyte and I am trying to learn and improve my knowledge by insisting on finding solutions to the path I have set for myself.
I believe that the proposed measures do not work because they lack a function that changes the filter context as ALL.
But, as I said in the initial post, if we add ALL, we also bring in the dates of double payments in the month.
Quite a dilemma.
Here is the link to the file
https://drive.google.com/file/d/1Aw73Thv-iMAdaSAX5p0ipnMw5BFUcVbL/view?usp=drive_link

I have made it free to access and edit the.pbix file, please try the solution directly in the file.
Best regards

[lbendlin]

link requires access, please check.

[gennaro_19]

hi @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.

[gennaro_19]

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

[lbendlin]

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_19]

hi @lbendlin 

Above you wrote that you did not fully understand what I meant to do.

Now that I have the two Running Totals I have finally been able to calculate, time by time, the interest rate, simple for periods of less than a year and compound for longer periods.
And all the calculations concerning the valuation and the development of the share value are based on the valuation dates column while everything concerning the amount of payments/disbursements is based on the payments/disbursements column.

In the image you can see that the two types of measurements coexist without interference. The empty rows are the dates of the payments that are not directly reflected in the valuation dates and are neutral for the purposes of calculating running totals and interest rates.

I think this is an important result because with the DAX alone I have left the source of my data unchanged without neglecting my objectives.

I hope I have helped you understand.

Kind regards.

[gennaro_19 · Accepted solution]

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 result

Thank you