Problem with Previous YTD for Ranked Sales

[m_wex]

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

[Jihwan_Kim]

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]

Good Evening,

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

Using version from October 2020.

Thanks,

M_Wex

[Jihwan_Kim]

Hi, @m_wex 
Thank you for your feedback.

Sorry, and I am not able to downgrade mine.

[m_wex]

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

[Jihwan_Kim]

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]

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.

[m_wex]

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.