Forum Discussion

m_wex's avatar
m_wex
Icon for Helper I rankHelper I
5 years ago

Problem with Previous YTD for Ranked Sales

Good Evening All,   Reaching out for help. Having no success solving an issue with calculating previous YTD sales (PYTD). The issue starts with current year sales and trying to match sales for the ...
Previous YTD for Ranked Sales
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, m_wex 
Thank you for your feedback.

Sorry, and I am not able to downgrade mine.

m_wex's avatar
m_wex
Icon for Helper I rankHelper I
5 years ago

Is it possible to post the dax formulas? It might be a while before I get my version upgraded.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, m_wex 

    Thank you for your message.

    Please check the below.

     

    Total Sales =
    SUM(Sales[Sales])
     
     
    CY Ranked Customer Sales =
    VAR rankingdimemsion =
    VALUES ( Customers[Customer] )
    VAR rankingselect =
    SELECTEDVALUE ( RankGroup[Indicator], 5 )
    RETURN
    CALCULATE (
    [Total Sales],
    FILTER (
    rankingdimemsion,
    RANKX ( ALL ( Customers[Customer] ), [Total Sales],, DESC ) <= rankingselect
    )
    )
     
     
    Test YTD =
    VAR lastsalesdate =
    MAXX ( ALL ( Sales[Date] ), Sales[Date] )
    VAR ytdsales =
    TOTALYTD ( [CY Ranked Customer Sales], Dates[Date] )
    RETURN
    IF ( MAX ( Dates[Date] ) <= lastsalesdate, ytdsales, BLANK () )
     
     
    PYTD =
    CALCULATE( [Test YTD], DATEADD(Dates[Date],-1,YEAR))
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • m_wex's avatar
      m_wex
      Icon for Helper I rankHelper I
      5 years ago

      Hi Jihwan Kim, 

       

      Sorry for the delayed feedback on this. I had to take a pause for another project.

      How do you filter out the blanks on the results without using a filter. The totals are affected when using ISNOTNULL on the YTD Sales, this happens when you look at all sales. You can have a blank in the current year but sales in the previous year which is why filtering out ISNOTNULL does not work.