Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
I need to calculate running total ,year wise. If user selects date as "Dec 2023" it should show running total from Jan 2023- Dec 2023.
Few examples are given below
User selection (in slicer dropdown) | Running Total |
Jan '2023 | To show data for Jan '2023 |
June '2023 | Sum from Jan to June 2023 |
October '2023 | Sum from Jan to October 2023 |
Dec '2023 | Sum showing Jan to Dec 2023 |
Jan '2024 | Sum for Jan '2024 |
Mar '2024 | Sum of Jan + Feb + Mar 2024 |
It is not YTD calculation but it is calendar year calculation. What logic I need to write-in? Pleae note that Month filter is in form of "December 2023" i.e. Month Year
Solved! Go to Solution.
Hi @romilv1 ,
@Dangar332 's answer is good , and there is my solution:
Below is my table:
The following DAX might work for you:
Measure =
var A = SELECTEDVALUE('Table'[Month])
var B = SELECTEDVALUE('Table'[Year])
RETURN
CALCULATE(SUM('Table'[data]),'Table'[Year] = B && 'Table'[Month]<=A)
The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider accept it as the solution to help the other members find it more quickly.
Hi @romilv1 ,
@Dangar332 's answer is good , and there is my solution:
Below is my table:
The following DAX might work for you:
Measure =
var A = SELECTEDVALUE('Table'[Month])
var B = SELECTEDVALUE('Table'[Year])
RETURN
CALCULATE(SUM('Table'[data]),'Table'[Year] = B && 'Table'[Month]<=A)
The final output is shown in the following figure:
Best Regards,
Xianda Tang
If this post helps, then please consider accept it as the solution to help the other members find it more quickly.
hi, @romilv1
try below code
var a = selectedvalue('dim date'[monthYear])
var b = calculate(min('dim date'[year]),'dim date'[monthYear]=a)
return
calculate(sum(vw_AdaptivePlanningActualsNew[Amount]),'dim date'[year]= b ,'dim date'[monthYear]<=a,removefilters())
if still not work provide some sample data
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Dangar332 Thanks but it doesn't work. When I select Dec 2023 it returns the sum of Dec '23 only.
hi, @romilv1
try below measure code
it maybe work
make sure you have month column(1,2,3,4,5....) for jan-2023 it 1, feb-2023 it 2 and year column
measure =
var a = selectedvalue(datetable[month year])
var b = calculate(min(datetable[year]),datetable[month year]=a)
return
calculate(sum[value],datetable[year]= b ,datetable[month year]<=a )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |