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 have been googling and reading posts a lot, but I still can't find a solution.
The root of my problem is probably the way the running total is calculated, but I can't find the reason.
Year | Month | Day | LJ_MAX_SUM | RT_LJ_MAX_SUM | What I want |
2020 | January | 1 | 917 | 917 | 917 |
2020 | January | 2 | 907 | 1731 | 1824 |
The data of the last 5 years:
Year | Month | Day | Sum per day |
2016 | January | 1 | 543 |
2016 | January | 2 | 664 |
2017 | January | 1 | 515 |
2017 | January | 2 | 585 |
2018 | January | 1 | 712 |
2018 | January | 2 | 907 |
2019 | January | 1 | 917 |
2019 | January | 2 | 814 |
2020 | January | 1 | 1104 |
2020 | January | 2 | 1180 |
LJ_MAX_SUM=
VAR Temp=
UNION(
ROW("test2", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-1,YEAR))),
ROW("test3", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-2,YEAR))),
ROW("test4", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-3,YEAR))),
ROW("test5", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-4,YEAR))),
ROW("test6", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-5,YEAR))),
ROW("test7", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-6,YEAR))),
ROW("test8", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-7,YEAR))),
ROW("test9", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-8,YEAR))),
ROW("test10", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-9,YEAR))),
ROW("test11", CALCULATE(SUM(data[Value]),DATEADD('Calendar_future'[Date],-10,YEAR)))
)
RETURN
MAXX(Temp, [test2])/1000
RT_LJ_SUM_MAX = CALCULATE (
[LJ_MAX_SUM],
FILTER (
ALLSELECTED( 'Calendar_future'),
'Calendar_future'[Date] <= MAX ( 'Calendar_future'[Date] )
)
)
The measure ''RT_LJ_SUM_MAX' takes the green and blue marked value and calculates the running total. What I want is, that it takes the green and red value to calculate the running total.
How can I do this?
Really appreciate your help.
Solved! Go to Solution.
Hello @adrianrupp ,
Try this:
RT_LJ_SUM_MAX =
SUMX (
FILTER (
ALLSELECTED ( 'Calendar_future' ),
'Calendar_future'[Date] <= MAX ( 'Calendar_future'[Date] )
),
[LJ_MAX_SUM]
)
Or this:
RT_LJ_SUM_MAX =
SUMX (
FILTER (
ALLSELECTED ( 'Calendar_future' ),
'Calendar_future'[Date] <= MAX ( 'Calendar_future'[Date] )
&& Calendar_future[Date]. [Year] = MAX ( Calendar_future[Date].[ Year] )
),
[LJ_MAX_SUM]
)
Best regards
Icey
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Hello @adrianrupp ,
Try this:
RT_LJ_SUM_MAX =
SUMX (
FILTER (
ALLSELECTED ( 'Calendar_future' ),
'Calendar_future'[Date] <= MAX ( 'Calendar_future'[Date] )
),
[LJ_MAX_SUM]
)
Or this:
RT_LJ_SUM_MAX =
SUMX (
FILTER (
ALLSELECTED ( 'Calendar_future' ),
'Calendar_future'[Date] <= MAX ( 'Calendar_future'[Date] )
&& Calendar_future[Date]. [Year] = MAX ( Calendar_future[Date].[ Year] )
),
[LJ_MAX_SUM]
)
Best regards
Icey
If this post helps,then consider Accepting it as the solution to help other members find it faster.
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 |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |