Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am having trouble using the cumulative sum of a set period, I have looked at various solutions on here but with each that I use, it is suming the last 12 months as I wish, however this is applying this figure to every date range, ie if the balances summed 10/05/2020 is exactly the same as the balance summed 09/05/2021 (its a weekly balancing every Sunday).
The lastest attempt I am using is
Last Balance Date | Payment Reference | Payment Received | 12 Month Total Payment | What I want it to say!!! |
09/05/2021 | 12345 | 230 | 351.64 | 351.64 |
02/05/2021 | 12345 | 351.64 | 121.64 | |
25/04/2021 | 12345 | 121.64 | 351.64 | 121.64 |
The raw data has over 23000 reference numbers and goes back over 12 months, but I want the rolling total for each account to be other the last 12 months. Currently, every single record has the same figure throughout the table.
I have tried the same as above using a calculated table and using table = distinct(last balance date) but I get the same result. Any help greatly appreciated.
Solved! Go to Solution.
@DLROLLINGS , I am assuming this is a new column
Try
Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Last Balance Date] <=earlier(Balances[Last Balance Date]) && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)
or
Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)
Cheers Amit, worked like a charm.
For reference, I used the following:
Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Last Balance Date] <=earlier(Balances[Last Balance Date]) && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)
Cheers Amit, worked like a charm.
For reference, I used the following:
Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Last Balance Date] <=earlier(Balances[Last Balance Date]) && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)
@DLROLLINGS , I am assuming this is a new column
Try
Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Last Balance Date] <=earlier(Balances[Last Balance Date]) && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)
or
Annual Payment =
VAR EarliestDate = Balances[Last Balance Date]-365
RETURN
CALCULATE(
SUM(Balances[Payments Received]),
FILTER(Balances,Balances[Last Balance Date]>EarliestDate && Balances[Payment Reference]=earlier(Balances[Payment Reference]))
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |