Max of cumulative sum measure

[zzinoun]

Hello Everyone,

i wanted to display a measure that shows only a fraction of a cumulative sum based on Year field (e.g. 2022) 

I created the folowing measure but I don(t find the right result 

Running Total (2022) =

var a=

MIN ( 'API : EW (QTO)'[API : EW (FM).Update Date] )

VAR maxd =

CALCULATE ( MAX ( 'Table'[API : EW (FM).Update Date] ), ALL ( 'API : EW (QTO)' ) )

RETURN

IF (

    a <= maxd,

         CALCULATE (SUM ( 'API : EW (QTO)'[QTO.Total Volume]),

         FILTER ( ALL ( 'API : EW (QTO)'), 'API : EW (QTO)'[API : EW (FM).Update Date]<= a ),

         FILTER ( ALL ( 'API : EW (QTO)'), 'API : EW (QTO)'[API : EW (FM).Update Date].[year]=2022)

)

)

as you can see below February should be 11.2M not 3M. what did I miss here ?

Thanks

[amitchandak · Accepted solution]

@zzinoun , If you have a date I will suggest YTD

example with help from date tbale

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

using year and month

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))

using day of year

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))

you can use allselected in place of all if needed

[zzinoun · Accepted solution]

I don't have a date table only a column.

is there something to add to the measure in order to retrieve only the max value per month ?

thanks 

[amitchandak · Accepted solution]

@zzinoun , If you have a date I will suggest YTD

example with help from date tbale

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

using year and month

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))

using day of year

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Day of Year] <= Max('Date'[Day of Year]) ))

you can use allselected in place of all if needed