Help adjusting measure to show last year sales on each date, instead of rolled up YTD sales.

[rlupe]

Hi,

I am getting the correct data for the Total, though I now want to see sales TY and LY at the day level. The issue is that some data is not populating and I can't tell why (red circle should be $5,417.16). Also, the Total is taking the latest data as the Total instead of summing (blue circle).

Below are the measures I am using. My date table has 'date' and 'fiscaldaylastyear_date' which I am trying to leverage. The only other parameter I use in the visual is to only show Fiscal Year Dates.

1 TY YTD CPO = var _ThisYear = [This Fiscal Year]
RETURN
CALCULATE(
    SUM(sap_sales_detail[CPO])
    ,'Date'[fiscalyear_id] = _ThisYear
)

_____________________________________________________________

1 LY YTD CPO =
var _LastYear = [Last Fiscal Year]
var _SameDateLastYear = [Last Year Date]
RETURN
CALCULATE(
    SUM(sap_sales_detail[CPO])
    ,'Date'[fiscalyear_id] = _LastYear
        && 'Date'[date] <= _SameDateLastYear
)

_____________________________________________________________

This Fiscal Year = CALCULATE(
    MAX('Date'[fiscalyear_id])
    ,'Date'[date] = TODAY()
)

_____________________________________________________________

Last Year Date =
var _LastBilling_Date = [Latest Data]
Return
CALCULATE(
    MAX('Date'[fiscaldaylastyear_date])
    ,'Date'[date] = _LastBilling_Date
)

_____________________________________________________________

Last Fiscal Year = [This Fiscal Year]-1

_____________________________________________________________

3 LY YTD CPO =
var _LastYear = [Last Fiscal Year]
var _SameDateLastYear = [Last Year Date]
RETURN
CALCULATE(
    SUM(sap_sales_detail[CPO])
    ,'Date'[fiscalyear_id] = _LastYear
        && 'Date'[date] = _SameDateLastYear
)

[audreygerred]

Hello! Try the below:

YTD TY=

TOTALYTD([Measure], 'Date'[Date])

YTD LY =

CALCULATE ([YTD TY], SAMEPERIODLASTYEAR ('Date'[Date]))

[rlupe]

I can't use sameperiodlastyear here because it is not the same day as last year. We are a day behind fiscally this year, next year could be 2 days behind - I would like to do this dynamically. That is why I am trying to leverage the fiscaldaylastyear_date column within my date table. Might not need a YTD formula for this, not sure.