Same Period Last Year - Full Year & PiT in same table

[Tob_P]

Hi,

I'm looking to two sale previous year measures in the same column. Sales for the full year and sales to the same day last year. Normally I wouldn't have the need to include both in a table, and would have two cards using the following simple DAX.

Sales Same Period Last Year = CALCULATE([Amount Measure], SAMEPERIODLASTYEAR('Date Table'[Date]))

...and to display sales to the same day last year, I would drop in calculated column as a visual level filter for

Is Today or Before = True

How can I amend the DAX above so that it only calculates to the sameperiod last year? Have tried a few amendments suc as TODAY but no luck.

[DimCozy]

Hello,

First of all you should create a Date Dimension and link that to your table that has the Sales. So based on the PoC, that i have done for your question i have created two tables:

1. The Date Dimension

2. The Sales Table

In the data model view i have conneted the date dimension to the Sales table. (key is Date)

And in the report view i have added a Slicer for date and a table having 3 columns

1. Sum of Sales (the sum of sales for the date range selected)

2. SalesSamePeriodLY

SalesSamePeriodLY = 
    CALCULATE(
        SUM(sales_powerbi_answers[Sales]),
        SAMEPERIODLASTYEAR('Date'[Date])
    )

which according to the date data and if the dates available SAMEPERIODLASTYEAR(), calculates the exactly same period but for the previous year, based on the selected data.

3. SalesFullLastYear

SalesFullLastYear = 

var _Current_Year = CALCULATE(MAX('Date'[Date]),ALLSELECTED())
var _Previous_Year = YEAR(_Current_Year) - 1

RETURN

CALCULATE(
    SUM(sales_powerbi_answers[Sales]),
    YEAR('Date'[Date]) = _Previous_Year
)

which calculates the max available date and based on that calculates the sum of sales for the previous year.

The result (as you can see the Full Last Year remains constant and the Same Period Last Year changes based on the range selected)

i hope this helps.

[Tob_P]

@DimCozy 

Thanks for this - appreciated, but would not provide a solution in my case as we have ascertained that I need to be able to incorporate the start of the Fiscal Year (01/05) into my solution somewhere. I'm fine with my SamePeriodLastYear measure which reflects yours, but ideally need to be able to adapt the DAX Johnt75 had suggested before to accept the start of the FY - have been trying various different ways with no success. Thanks again.

[johnt75]

If you have values from your date table in the visual then you don't need the bit about today, you could just use

Sales Same Period Last Year =
CALCULATE ( [Amount Measure], DATEADD ( DATESYTD ( 'Date'[Date] ), -1, YEAR ) )

[Tob_P]

Thanks again @johnt75 

Gave that a go also but still coming back with unexpected results...

Test is the amended DAX you suggested so coming back with a lower figure than previously, but the card that you see overlayed shows the sales same period last year to the day figure which is zero.

[johnt75]

Which columns from the date table are you using in the visual? I would expect a YTD figure to grow over time.

You could add a measure like

Num Visible Days =
COUNTROWS ( DATEADD ( DATESYTD ( 'Date'[Date] ), -1, YEAR ) )

to show whether the correct dates are being calculated.

[Tob_P]

Great suggestion and it flags what the issue is here - it's counting back to the start of the calendar year as opposed to the Financial Year that I'm working on! My FY is 01/05/ - 30/04 and that is set up using a calc column...

Financial Year =

VAR FY =

    IF (

        MONTH ( 'Date Table'[Date] ) <= 4,

        VALUE ( FORMAT ( 'Date Table'[Date], "YYYY" ) ) - 1,

        VALUE ( FORMAT ( 'Date Table'[Date], "YYYY" ) )

    )

RETURN

    CONCATENATE ( "FY ", CONCATENATE ( FY, CONCATENATE ( "/", FY + 1 ) ) )

This obviously isn't a date field so not sure if your suggestion can be amended to reflect that start of the FY?

[Tob_P]

Hi @johnt75 

Thanks for the suggestion and gave that a go, but definitely some unexpected results in there...

DTS SAles PY (FullYear) is essentially the SamePeriodLast Year Measure without the calculated column visual level filter applied and I know those figures are good. TEST column in your suggested Measure - you will see the second line down that it calculates Sales approx 18k above the whole of the previous year.

[johnt75]

Try

Sales Same Period Last Year =
CALCULATE (
    [Amount Measure],
    CALCULATETABLE (
        DATEADD ( DATESYTD ( 'Date'[Date] ), -1, YEAR ),
        TREATAS ( { TODAY () }, 'Date'[Date] )
    )
)