Previous fiscal year to date - how to limit to only the same period as has elapsed this year

[petejigsaw]

This is probably a very simple question to answer, but it's driving me nuts!

I want to show cards that illustrate [Total Sales] for 'Fiscal Year to Date' alongside [Total Sales] for 'Previous Fiscal Year to Date', where the previous year's card is limited to only showing the sales for the SAME number of days that have elapsed in this fiscal year.

I have tried every possible combination of DAX formulas and filters I can think of, but none give me the outcome I want.

Our fiscal year is 1 Feb to 31 Jan, and my measures are:

Total Sales = SUM('Summary Sales Data'[SalesValueIncVAT])
Sales - FYTD = CALCULATE([Total Sales],DATESYTD('UK Date Table'[Date],"31/1"))
Sales - LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('UK Date Table'[Date]))
Sales - LFYTD = CALCULATE([Sales - LY],DATESYTD('UK Date Table'[Date],"31/1"))

I am using the standard Enterprise DNA Extended Date Table.

Can anyone help?

Thanks in anticipation.

[petejigsaw]

Thank you @v-yanjiang-msft 

It was my error - I had not created a relationship betwen the new 'Table' [Date] field and the 'Summary Sales Data' [Date] table. 🙄

All working now.

[v-yanjiang-msft]

Hi @petejigsaw ,

According to your description, here's my solution. Create a measure:

Sales - LFYTD =
CALCULATE (
    [Total Sales],
    SAMEPERIODLASTYEAR (
        FILTER (
            DATESYTD ( 'UK Date Table'[Date], "31/1" ),
            [Date] <= MAXX ( ALL ( 'Summary Sales Data' ), [Date] )
        )
    )
)

Get correct result in my sample:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[petejigsaw]

Thanks for your suggested solution. However I can't seem to get it to work with my data. The cards simply show "--" for both 'Sales - FYTD' and 'Sales - PFYTD' instead of the expected output.

Here is a link to the PBIX with my data, in the hope you can help.

Sales Analysis 

Thank you in anticipation.

[v-yanjiang-msft · Accepted solution]

Hi @petejigsaw ,

The reason your data is empty is the DATESYTD function will return the max date according to context if you put the measure in a card visual.

Here's my solution.

1.Create a new table.

Table =
SELECTCOLUMNS (
    FILTER (
        'UK Date Table',
        'UK Date Table'[Date]
            <= MAXX ( 'Summary Sales Data', 'Summary Sales Data'[Date] )
    ),
    "Date", [Date]
)

2.Modify the measures.

Sales - FYTD =
CALCULATE ( [Total Sales], DATESYTD ( 'Table'[Date], "31/1" ) )

Sales - LFYTD =
VAR _t =
    FILTER (
        SAMEPERIODLASTYEAR ( DATESYTD ( 'Table'[Date], "31/1" ) ),
        [Date]
            <= DATE ( YEAR ( MAX ( 'Summary Sales Data'[Date] ) ) - 1, MONTH ( MAX ( 'Summary Sales Data'[Date] ) ), DAY ( MAX ( 'Summary Sales Data'[Date] ) ) )
    )
RETURN
    CALCULATE ( [Total Sales], _t )

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[petejigsaw]

Thanks @v-yanjiang-msft 

However, the first step in your solution is throwing an error, indicating the "Date" element in the last part is incorrect. Should this instead reference another field?

Thanks in anticpation.

[v-yanjiang-msft]

Hi @petejigsaw ,

Does the error occur in the pbix file I attached? I haven't seen it. If in other files, please attach it or provide a snapshot about the error.

Best regards,

Community Support Team_yanjiang