cancel
Showing results for
Did you mean:
Frequent Visitor

Max of cumulative sum measure

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

2 ACCEPTED SOLUTIONS
Super User

@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

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Frequent Visitor

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

2 REPLIES 2
Frequent Visitor

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

Super User

@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

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements