topic Re: Problem with Previous YTD for Ranked Sales in DAX Commands and Tips

Problem with Previous YTD for Ranked Sales

m_wex — Tue, 27 Apr 2021 04:27:29 GMT

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

Re: Problem with Previous YTD for Ranked Sales

Jihwan_Kim — Tue, 27 Apr 2021 05:14:27 GMT

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

Re: Problem with Previous YTD for Ranked Sales

m_wex — Wed, 28 Apr 2021 01:51:45 GMT

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.

Re: Problem with Previous YTD for Ranked Sales

m_wex — Thu, 29 Apr 2021 00:17:09 GMT

Good Evening,

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

Using version from October 2020.

Thanks,

M_Wex

Re: Problem with Previous YTD for Ranked Sales

Jihwan_Kim — Thu, 29 Apr 2021 04:05:07 GMT

Hi, m_wex
Thank you for your feedback.

Sorry, and I am not able to downgrade mine.

Re: Problem with Previous YTD for Ranked Sales

m_wex — Fri, 30 Apr 2021 02:05:16 GMT

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

Re: Problem with Previous YTD for Ranked Sales

Jihwan_Kim — Fri, 30 Apr 2021 03:18:48 GMT

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

Re: Problem with Previous YTD for Ranked Sales

m_wex — Fri, 14 May 2021 04:20:58 GMT

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.