Forum Discussion

Varishtha's avatar
Varishtha
Helper I
2 years ago

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

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:

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

6 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    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's avatar
      Varishtha
      Helper I
      2 years ago

      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:

       

       

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    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's avatar
      Varishtha
      Helper I
      2 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Varishtha
      Helper I
      2 years ago

      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.