Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Total YTD calculation getting wrong

Hi,

 

Please find the atttached image ,

 

I want output as for Jan it should show  300 (100+200) in the MTD column, for Feb 500 (300+200) , March 1200 ( 300+500+400) and so on..

 

Could you please help me on this ?

 

Thanks,

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create calculated column.

    count = COUNTX(FILTER(ALL('Table'),'Table'[Month]=EARLIER('Table'[Month])),[ID])

    2. Create measure.

    Flag1 =
var _1=
IF(
    MAX('Table'[count])>=1,
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[ID]=MAXX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[ID]))),
MAX('Table'[Sales])
)
return
IF( MAX('Table'[count])>=1&&MAX('Table'[ID])=MAXX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[ID]),_1,BLANK())
    Flag2 =
var _1=
IF(
    MAX('Table'[count])>1,
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[ID]<=MAX('Table'[ID]))),
IF(
    MAX('Table'[ID])=3,
SUMX(FILTER(ALL('Table'),'Table'[ID]<=MAX('Table'[ID])),[Flag1]),MAX('Table'[Sales])))
return
IF(  MAX('Table'[count])>=1&&MAX('Table'[ID])=MAXX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[ID]),_1,BLANK())
    Measure =
IF(
    MAX('Table'[ID])<=3,[Flag2],
SUMX(FILTER(ALL('Table'),'Table'[ID]<=MAX('Table'[ID])),[Flag2]))

    3. Result:

     

      

    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

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Anonymous when you are working with the TIme Intelligence functions like TOTALYTD, it is a best practice to add a calendar dimension in your model and then uses that for your calculations. You can check my series on time intelligence here and make changes accordingly.

     

    https://www.youtube.com/playlist?list=PLiYSIjh4cEx3GXH6WnT8yIQCPGC5jmoPL

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create calculated column.

    count = COUNTX(FILTER(ALL('Table'),'Table'[Month]=EARLIER('Table'[Month])),[ID])

    2. Create measure.

    Flag1 =
var _1=
IF(
    MAX('Table'[count])>=1,
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])&&'Table'[ID]=MAXX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[ID]))),
MAX('Table'[Sales])
)
return
IF( MAX('Table'[count])>=1&&MAX('Table'[ID])=MAXX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[ID]),_1,BLANK())
    Flag2 =
var _1=
IF(
    MAX('Table'[count])>1,
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),'Table'[ID]<=MAX('Table'[ID]))),
IF(
    MAX('Table'[ID])=3,
SUMX(FILTER(ALL('Table'),'Table'[ID]<=MAX('Table'[ID])),[Flag1]),MAX('Table'[Sales])))
return
IF(  MAX('Table'[count])>=1&&MAX('Table'[ID])=MAXX(FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])),[ID]),_1,BLANK())
    Measure =
IF(
    MAX('Table'[ID])<=3,[Flag2],
SUMX(FILTER(ALL('Table'),'Table'[ID]<=MAX('Table'[ID])),[Flag2]))

    3. Result:

     

      

    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