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
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.