Forum Discussion
Helper IIILast Year Sales calculation with custom dates
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.
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
4 Replies
- themistoklis
Community Champion
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")
Jameswh91Many 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.
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
- danextian
Super User
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/
