Last Year Sales calculation with custom dates

[Jameswh91]

Hi, I'm looking to create a dax calculation that has last years financial ytd sales. I'm ideally looking for a formula that uses the start of the year as 1st May.

Many thanks in advance for any help received. 

[danextian]

Hi @Jameswh91 ,
You can use either of these measures for the YTD:

MyYTD1 =
CALCULATE ( [MyMeasure], DATESYTD ( Dates[Date], "Apr 30" ) )

MyYTD2 =
TOTALYTD (  [MyMeasure], Dates[Date], "Apr 30" ) 

And these measures for YTD LY

MyYTD1 LY =
CALCULATE ( [MyYTD1 ], SAMEPERIODLASTYEAR ( Dates[Date] ) )

MyYTD1 LY =
CALCULATE ( [MyYTD1 ], DATEADD ( Dates[Date], -1, YEAR ) )

 This article is a good read when deciding between TOTALYTD and DATESYTD: https://www.sqlbi.com/blog/marco/2018/08/10/the-hidden-secrets-of-totalytd/

[Anonymous · Accepted solution]

Hi @Jameswh91 ,

First create an independent yearmonth table as slicer.

calendar = distinct('Table'[yearmonth])

Then create a measure like below:

custom_LYTD =
CALCULATE (
    SUM ( 'Table'[value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        FORMAT ( EDATE ( 'Table'[date], 12 ), "YYYYMM" )
            >= SELECTEDVALUE ( 'calendar'[yearmonth] )
            && 'Table'[yearmonth] < SELECTEDVALUE ( 'calendar'[yearmonth] )
    )
)

If I misunderstood your meaning, please share some sample data and expected result.

Best Regards,

Jay

[Jameswh91]

Many thanks for your reply, looking at things closer, I think i'm looking for a calculation that shows the sales amount for each month of the previous year run in my mon-year line graph.

Is this possible?

Again many thanks in advance for any help received.

[themistoklis]

@Jameswh91 

Try using the TOTALYTD function where you specify the year end date.

https://docs.microsoft.com/en-us/dax/totalytd-function-dax

Try something like the formula below:

SALES YTD LY = TOTALYTD(sum('table'[Sales]),DATEADD(Dates[Date],-12,MONTH),"4/30")