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 same period of the previous year. YTD sales works fine but PYTD returns the entire year result which does not work for comparison reasons. 

 

I am using the following formula to create a customer ranking. This formula works fine and provides a list of the TOPN customers for the selected date.:

 
CY Ranked Customer Sales =
VAR
RankingDimesion=Values(customers[customer name] )
Var
RankingSelect = [Ranking Select PN]   //(Selection criteria Table with Top 5, Top 10, Top 25, Top 50, All)
Return
CALCULATE([Total Sales],
Filter( RankingDimesion,
RankX(all(Customers[Customer Name] ), [Total Sales], ,desc ) <= RankingSelect))
 
The data from the above formula is then used in the next formula to produce YTD sales. The calculation looks at the last date in the invoiced sales table for YTD sales and seems to work fine. 
 
Test YTD  =
VAR lastsaledate = maxx(all(Sales[Calendar day]), Sales[Calendar day])
VAR ytdsales = TOTALYTD([CY Ranked Customer Sales],
Dates[Date])
Return
if(min(Dates[Date])<=lastsaledate,ytdsales,blank() )

 

The desired result is the the PYTD sales for the ranked customers shown for the same time period as YTD sales. So if YTD sales are between 1/01/21 and 3/31/21 the PYTD should be for the same date period.  The added catch is to show only PYTD sales for the TOPN customers (Top 5, 10, 25, 50, All as selected in the first formula) for the current period. The result should look like the following for the selection (TopN = 5 (YTD 1/1/21 - 3/31/21)).

 

Customer     YTD Sales   PYTD Sales

A                   $100.00      $98.00

B                    $50.00       $52.00

C                    $40.00       $45.00

D                    $20.00       $20.00

E                     $10.00       $ 5.00

 

This problem has been driving me nuts and impacts many of my other YTD calculations in other tabs of the report. Any help would be greatly appreciated.

 

M_Wex

Previous YTD for Ranked Sales

7 Replies

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

    Hi, m_wex 

    Please check the below picture and the sample pbix file's link down below.

    I tried to create a sample pbix file based on the scenario, and I hope it suits your case.

    All measures are in the sample pbix file.

     

     

     

    https://www.dropbox.com/s/0akngbte7h692cc/Mwex.pbix?dl=0 

     

    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

      Good Evening,

       

      Thank you for the post and the sample data set. I am not able to view this yet as the version I am using is not the latest (October 2020) and I need to check with our IT group about updating to the April 2021 version.

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

      Good Evening,

       

      Is it possible to send another version of the sample data? 

      Using version from October 2020.

       

      Thanks,

      M_Wex

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