Cumulative stops every year. I do not want that

[PrinceII]

Every year the cumulative resets. I dont want that rest every year. I use the dax query

CUMULATIVE Total =

CALCULATE(

COUNT('ALL_PROJECTS_RPT'[CONTRACTOR]),

FILTER(

ALL('ALL_PROJECTS_RPT'),

( 'ALL_PROJECTS_RPT'[Week] <= MAX('ALL_PROJECTS_RPT'[WeekCum])

)

)

)

[VahidDM · Accepted solution]

Hi @PrinceII 

I'm pretty sure @Greg_Deckler  solution will solve your problem, but a solution comes to my mind as below:

First add a new Column to your table:

YearWeek =
Var _W = if('ALL_PROJECTS_RPT'[Week]<10,0&'ALL_PROJECTS_RPT'[Week],'ALL_PROJECTS_RPT'[Week])
return
'ALL_PROJECTS_RPT'[Year]&_W

Then use it in measure:

CUMULATIVE Total =
CALCULATE(
COUNT('ALL_PROJECTS_RPT'[CONTRACTOR]),
FILTER(
ALL('ALL_PROJECTS_RPT'),
'ALL_PROJECTS_RPT'[YearWeek] <= Max(ALL_PROJECTS_RPT'[YearWeek]
)
)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Mikelytics]

@PrinceII 

I think the reason is because you apply the

( 'ALL_PROJECTS_RPT'[Week] <= MAX('ALL_PROJECTS_RPT'[WeekCum])

on the week column. When a new year starts your reference values drops from 50 to 1 which lets DAX only consider the values <= 1. But when you use the date column it should work.

Why do you not use the date column with a date table?

CALCULATE(
    [Measure],
    FILTER(
        ALL('Date'),
        'Date'[Date]<=MAX('Date'[Date])
    )
)

________________________

If this post helps, then please Accept it as the solution to help other community members find it more quickly

Click on the Thumbs-Up icon if you like this reply.

[PrinceII]

@Mikelytics when i do that I dont get what I want

[Mikelytics]

Can you please try it with the same slicer you used before?

and thena lso witht the same axis?

[Greg_Deckler]

@PrinceII I would recommend creating a Sequential week column like this: Sequential - Microsoft Power BI Community

[PrinceII]

Hi @Greg_Deckler , I created the sequential. not sure about the next steps. As would still want to see the weeks and year in de axis. 

[Greg_Deckler]

@PrinceII I'm thinking you would replace that last part of your formula with sequential.

CUMULATIVE Total =

CALCULATE(

COUNT('ALL_PROJECTS_RPT'[CONTRACTOR]),

FILTER(

ALL('ALL_PROJECTS_RPT'),

( 'ALL_PROJECTS_RPT'[Week] <= [sequential]

)

)

)