Cumulative function dateytd() isn't working as it should.

[Varishtha]

Below is the measure(v_Cum. Est Umsatz BK), on which for which I wanted to calculate cumulative values(Year to date). But instead it's giving me total sum for the year. 

v_Cum. Est Umsatz BK = CALCULATE(

    [v_Est Umsatz BK]

    ,DATESYTD(Calender[Dates])

    )

 Other measures related to v_Cum. Est Umsatz BK:

v_Est Umsatz BK = [v_% prev_year_contribution BK]*SUM(Target[Sales target -  BK])

v_% prev_year_contribution BK =var total_bk_prev_year = CALCULATE([v_ prev_year_UMSATZ_BK],ALL(Calender[Start of Month]))

RETURN DIVIDE([v_ prev_year_UMSATZ_BK],total_bk_prev_year,0)

v_ prev_year_UMSATZ = CALCULATE([UMSATZ],DATEADD(Calender[Dates],-1,YEAR))

Below you can see what's actually happening in the table:

[Anonymous]

Hi,

Thanks for the solution @Ritaf1983  provided, and i want to offer some more information for user to refer to.

hello @Varishtha , based on your description, you can consider to use a alternative solution, you can try the following measure.

v_Cum. Est Umsatz BK =
IF (
    [v_Est Umsatz BK] <> BLANK (),
    CALCULATE (
        [v_Est Umsatz BK],
        ALLSELECTED ( 'Calendar'[Dates] ),
        YEAR ( 'Calendar'[Dates] ) = YEAR ( MAX ( 'Calendar'[Dates] ) ),
        'Calendar'[Dates] <= MAX ( 'Calendar'[Dates] )
    )
)

Best Regards!

Yolo Zhu

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

[Varishtha]

Hi @Anonymous ,/\
I tried using the measure, but as you can see in the ss below, I am still getting total value, instead of cumulative values.

[Ritaf1983]

Hi @Varishtha 
Yes if you use all() functions on your fact table / dim date table it removes all filters, so the result cannot be filtered by date...
This is the reason for having all the sum for every date...

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

[Varishtha]

Hello @Ritaf1983 ,
But did you check my measues, I have used all() in the measure - v_% prev_year_contribution BK , but this measure was also used with the measure "v_Est Umsatz BK" and gave expected results.
Then why is the "v_Cum. Est Umsatz BK" causing isuues.
P.S., all mentioned measures have been defined in my OG post.

[Ritaf1983]

Hi @Varishtha 

You can use TOTALYTD function, please refer to the linked tutorial :

https://www.youtube.com/watch?v=0yHOiGXoi9w

For more detailed suggestion

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test the coding formula.

How to Get Your Question Answered Quickly  

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

[Varishtha]

Hey @Ritaf1983 ,
It's difficult, but I will try to arrange sample data. Meanwhile I tried TotalYTD and it failed.
TotalYTD is also giving total sum of the year for each month.
Is it possible that, because in the measures I am using ALL() function.
Pls see the snap below: