YTD LY Returning full month

[olivere91]

Hello,

I'm trying to get LY YTD sales writing this formula. I can confirm the Total Sales YTD formula works, but when I try to use the formula below I'm getting sales totals through the end of February 2021 instead of through February 7th, 2021.  What am I doing wrong?

Total Sales YTD LY =

CALCULATE(Item_Sales_Profit[Total Sales YTD],SAMEPERIODLASTYEAR(Dates[Date]))

[sevenhills]

Since you are sure about your measure, create a new table as below (for testing) and check the dates data

RETURN
    CALCULATETABLE (
        SAMEPERIODLASTYEAR ( 'Dates'[Date] ),
        'Dates'[Date] = TODAY() 
    )

[TheoC]

Hi @olivere91 

Do you get the correct output if you adjust your measure to:

Total Sales YTD LY = CALCULATE ( [Total Sales YTD] , DATEADD ( 'Dates'[Date] , -1 , YEAR ) )

Hope this works.

Theo 🙂

[olivere91]

Hey TheoC,

No, I get the same total as before, so a total through EOM Feb 2021. 

[TheoC]

@olivere91 no worries.  

What are you running for the Current Year To Date measure?  Is it:

Cur Yr to Date = TOTALYTD ( SUM ( 'Table'[Amount] ) , 'Date'[Date] )

or is it something else?

[olivere91]

@TheoC 

Total Sales YTD = TOTALYTD(Item_Sales_Profit[Total Revenue],Dates[Date])

Total Revenue = SUM(Item_Sales_Profit[Revenue])

[TheoC · Accepted solution]

@olivere91 okay, so the concept of DATESYTD and TOTALYTD etc don't really work mid periods.

I've put together the following for you and tested it so it works.  Probably not the best use of DAX but nonetheless will give you what you want:

Note that in the below, I've called my table "t" so please adjust accordingly. This measure also don't require a Date table. Just use the Date column from your Fact table.

CurYear = 

VAR _CurYear = YEAR ( TODAY () )
VAR _CurMth = MONTH ( TODAY() )
VAR _CurDay = DAY ( TODAY () )
VAR _LastDay = DATE ( _CurYear , _CurMth , _CurDay )
VAR _FirstDay = DATE ( _CurYear , 1 , 1 )
VAR _Calc = CALCULATETABLE ( VALUES ( 't'[Date] ) , FILTER ( 't' , 't'[Date] >= _FirstDay && 't'[Date] <= _LastDay ) ) 

RETURN

CALCULATE ( SUM ( 't'[Amount] ) , FILTER ( 't' , 't'[Date] >= _FirstDay && 't'[Date] <= _LastDay ) )

For the last year measure, just simply add "-1" to the end of the _CurYear variable like below:

LstYear = 

VAR _CurYear = YEAR ( TODAY () ) -1
VAR _CurMth = MONTH ( TODAY() )
VAR _CurDay = DAY ( TODAY () )
VAR _LastDay = DATE ( _CurYear , _CurMth , _CurDay )
VAR _FirstDay = DATE ( _CurYear , 1 , 1 )
VAR _Calc = CALCULATETABLE ( VALUES ( 't'[Date] ) , FILTER ( 't' , 't'[Date] >= _FirstDay && 't'[Date] <= _LastDay ) ) 

RETURN

CALCULATE ( SUM ( 't'[Amount] ) , FILTER ( 't' , 't'[Date] >= _FirstDay && 't'[Date] <= _LastDay ) )

Output for the above will look like:

 Sorry I didn't get this for you the first time round.

Cheers,

Theo