Forum Discussion

ChrisCoxhill's avatar
ChrisCoxhill
Frequent Visitor
2 years ago
Solved

Previous Year comparison - account for partial current Year

Hi,   Please can I have assistance when trying to write DAX comparing to the prior year? When Month is in scope, and for full prior years, the calculation returns the appriopriate amount for "Prev...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ChrisCoxhill ， amitchandak thank you for your prompt reply!
    Please try the following measure to show only the month totals for the same period last year:

    Previous Year 2 = CALCULATE(
   SUM('SW Energy'[Cost]),
   FILTER(
       SAMEPERIODLASTYEAR('Date'[Date]),
       MONTH('Date'[Date]) <= MONTH(MAX('SW Energy'[Invoice Date]))
   )
)

     Result:

    Best regards,

     

    Joyce

     

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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

ChrisCoxhill , Based on what I got, Try like

 

YTD Cost Previous (£) = 
VAR CostYTD =         CALCULATE(SUM('SW Energy'[Cost]),CALCULATETABLE(DATESYTD('Date'[Date]),'Date'[Relevant]))
VAR CostPreviousYTD = if(ISINSCOPE('Date'[Month]),  CALCULATE(SUM('SW Energy'[Cost]),CALCULATETABLE(DATESYTD(dateadd('Date'[Date],-1,YEAR)),'Date'[Relevant])),CALCULATE(SUM('SW Energy'[Cost]),DATESYTD(dateadd('Date'[Date],-1,YEAR))))

RETURN

CostPreviousYTD
ChrisCoxhill's avatar
ChrisCoxhill
Frequent Visitor
2 years ago

Hi, thank you for your reply!

 

I am sorry to ask you to tweak a calc please:

 

Previous Year Comp (%) = 
VAR CurrentCost = SUM('SW Energy'[Cost])
VAR CostPreviousYTD = CALCULATE(SUM('SW Energy'[Cost]),CALCULATETABLE(SAMEPERIODLASTYEAR('Date'[Date])),'Date'[Date]<=MAX('SW Energy'[Invoice Date]))

RETURN

DIVIDE(CurrentCost-CostPreviousYTD,CostPreviousYTD)

 

This may explain myself better and it shows up like this now:

 

 

You can see your column added at the end.

Everything with the ticks and crosses now works fine, just the Previous Year 2024, as we don't have a full year yet I just want to compare it to the YTD from 2023 as circled; £334. The answer should not be -59.6%, it should be +6.0% as per the first screenshot, but then that compromises the other full years comparisons 😞 

I don't know how to do this sorry and I greatly appreciate your assistance thank you!