YTD with 53 weeks

[PowerMarra]

Hi,

I want to calculate YTD and use this calculateion for different years. The YTD formula works fine for 2019 and 2021, but for 2020 it is a problem due to 53 weeks. It summuraize, but it will not show a subtotal thus it will not aggregate.

 

I tried another formula in order to take into account 53 weeks: 

YTD Value TEST =

var currentyear = SELECTEDVALUE('Calendar'[Iso Year])

var currentweek = SELECTEDVALUE('Calendar'[Week])

return

CALCULATE(Hovedmeasures[Value Sales],

fILTER(ALL('Calendar'),

'Calendar'[Iso Year] = currentyear && 'Calendar'[Week] <= currentweek)

)

But this will not summaraize either.

Does anyone know how to calculate YTD so I also can use it for those years with 53 weeks?

[Anonymous · Accepted solution]

Hi  @PowerMarra ,

I created some data:

Hovedmeasures：

Calendar：

The relationship between the two tables is one-to-one and Both.

Here are the steps you can follow：

1. Create measure.

YTD Value TEST =
CALCULATE(SUM('Hovedmeasures'[rand]),DATESYTD('Calendar'[Date]))

2. Result:

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

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

[amitchandak]

@PowerMarra , I think a measure like this should work , assuming [Value Sales] is a measure else use an aggregation like sum([Value Sales])

CALCULATE([Value Sales],
fILTER(ALL('Calendar'),
'Calendar'[Iso Year] = max('Calendar'[Iso Year]) && 'Calendar'[Week] <=  max('Calendar'[Week] ) )
)