Cumulative Total with date column

[romilv1]

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

[v-xiandat-msft · Accepted 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.

[romilv1]

measure77 =

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)

@Dangar332  Code from my file.

[Dangar332]

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.

[romilv1]

@Dangar332  Thanks but it doesn't work. When I select Dec 2023 it returns the sum of Dec '23 only.

[Dangar332]

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.