Cumulated YTD figures with Year-Calendar week???

[Orstenpowers]

Hi everybody!

My table looks as follows. Now I would like to add a column that illustrates the cumulated values since beginning of the year, based on the VEAR-CW (--> YEAR-CW = FORMAT('Date'[Date],"YYYY") & "-" & FORMAT(WEEKNUM('Date'[Date],21),"00")

).

As an example, I would like to get following results

2024-01   22.364.041,96

2024-02   67.539.975,34

2024-03   112.623.836,05

I tried it with the Quickmeasure provided by PBI Desktop, but that did not work...

Anybody here who could help me?

[v-zhouwen-msft]

Hi @Orstenpowers ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a column named ‘Rank’

Rank = 
var _a = 'Table'[YEAR]
RETURN IF(
    _a = 2024,
    RANKX(FILTER(ALL('Table'),'Table'[YEAR] = 2024),'Table'[YEAR-CW],,ASC,Dense),
     RANKX(FILTER(ALL('Table'),'Table'[YEAR] = 2023),'Table'[YEAR-CW],,ASC,Dense)
    )

2. Use the following DAX expression to create a column named ‘Cumulative value’

Cumulative value = IF('Table'[YEAR] = 2024,
'Table'[Total Amount(Base)] +CALCULATE(SUM('Table'[Total Amount(Base)]),FILTER(ALL('Table'),'Table'[Rank] <EARLIER('Table'[Rank]) && 'Table'[YEAR] = 2024)),
'Table'[Total Amount(Base)] +CALCULATE(SUM('Table'[Total Amount(Base)]),FILTER(ALL('Table'),'Table'[Rank] <EARLIER('Table'[Rank]) && 'Table'[YEAR] = 2023)))

3. Final output

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

[Orstenpowers]

Thank you very much for your reply!

Is it possible to adjust the formula, making it needless to write the years? "Recognizing" the year automatically?

Looking forward to your feedback I remain

[v-zhouwen-msft · Accepted solution]

Hi @Orstenpowers ,

Replace 'year' in the expression:

Rank = 
var _a = 'Table'[YEAR]
RETURN IF(
    _a = YEAR(TODAY()),
    RANKX(FILTER(ALL('Table'),'Table'[YEAR] = YEAR(TODAY())),'Table'[YEAR-CW],,ASC,Dense),
     RANKX(FILTER(ALL('Table'),'Table'[YEAR] = YEAR(TODAY()) - 1),'Table'[YEAR-CW],,ASC,Dense)
    )

Cumulative value = IF('Table'[YEAR] = YEAR(TODAY()),
'Table'[Total Amount(Base)] +CALCULATE(SUM('Table'[Total Amount(Base)]),FILTER(ALL('Table'),'Table'[Rank] <EARLIER('Table'[Rank]) && 'Table'[YEAR] = YEAR(TODAY()))),
'Table'[Total Amount(Base)] +CALCULATE(SUM('Table'[Total Amount(Base)]),FILTER(ALL('Table'),'Table'[Rank] <EARLIER('Table'[Rank]) && 'Table'[YEAR] = YEAR(TODAY()) - 1)))

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