Previous YTD Calculation

DAX DAX Calculations
1

I’m struggling with a previous YTD calculation. If I place the measures in a table to show results by month or totals by year (i.e. first column in the table is a date field), it provides the full monthly or full yearly totals. And when I try to use the formula to compare YOY progress for salespeople or items, it shows the PYTD as the entire year and not the same period from last year.

Here are my measures.

Total Sales =
CALCULATE(
SUMX(Billing,Billing[Ship_Dollars]),
USERELATIONSHIP(‘Dates’[Date],Billing[Ship_Date]))

Sales Year to Date =
CALCULATE(
[Total Sales] ,
DATESYTD( Dates[Date] ) )

I’ve tried two separate measures to get last year’s YTD numbers.
Sales Year to Date LY =
CALCULATE(
[Sales Year to Date],
SAMEPERIODLASTYEAR( Dates[Date] ))

Sales YTD LY =
CALCULATE(
[Sales Year to Date],
DATEADD( Dates[Date],-1,YEAR ))

I’ve also explored these options, with the same results.

Sales Period This Year =
VAR LastSalesDate = MAXX(All(Billing[Ship_Date]), Billing[Ship_Date])
VAR SalesThisYear = TOTALYTD([Total Sales], Dates[Date])
RETURN
If (MIN(Dates[Date])<= LastSalesDate, SalesThisYear, Blank() )

Sales Period Last Year =
IF(LASTDATE(Dates[Date])<=TODAY(),
CALCULATE([Sales Period This Year],
SAMEPERIODLASTYEAR(Dates[Date])),
BLANK()
)

Another consideration - I use a relative date filter on the report page to show items when the value is in this year.

I’m not able to upload a PBIX file since it contains sensitive company data. Any help is appreciated.

2

Hi @npeterson. Very difficult to investigate without seeing your work-in-progress. Please use sample data or see @BrianJ’s YouTube video on masking sensitive data, and upload the results along with an Excel mock-up of your desired outcome for the forum members to pursue.
Greg

3

Hi @npeterson, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
    image
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

4

I tried yet another formula and this one worked. It combines the [Sales LY] measure with a second CALCULATE.

Cumulative Sales LY =
CALCULATE(
CALCULATE( [Sales LY],
FILTER( ALLSELECTED( Dates ), Dates[Date] <= MAX( Billing[Ship_Date] ) ) ),
USERELATIONSHIP( Dates[Date], Billing[Ship_Date] ))

I don’t know why it works, but it does. Thanks all.