Current Year and Last Year

DAX DAX Calculations
1

Hi Team,
I tried calculating Last Year Act as follows:
LY Actual =
CALCULATE(SUM(‘SummaryUnpivotWithDate’[Value]), DATEADD(DateTable[Date],-1,YEAR),FILTER(SummaryUnpivotWithDate, SummaryUnpivotWithDate[Act/Bud] = “Act”))

        and my current year actual formula is as follows:
        CY Actual = 
        CALCULATE(SUM(SummaryUnpivotWithDate[Value]), 
        FILTER(SummaryUnpivotWithDate, SummaryUnpivotWithDate[Act/Bud]= "Act"))

unfortunately, I do not have figures for LY Actual calculation.
Please let me know the area to check.
thank you.
Regards,
Aye

2

try this code instead:

LY Actual =
CALCULATE ( [CY Actual], SAMEPERIODLASTYEAR ( DateTable[Date] ) )
3

Hi @ammu

Just a little change in your formula.

Use LY Actual = Calculate [CY Actual] , Dateadd [date] , -1 , Year

I’ve just typed the formula manually please use correct parenthesis.

Thanks
Ankit

4

Here are some sample codes that you can tweak to your preference

Cumulative Sales (Selected) = 
CALCULATE( 
	[Total Sales] , 
	FILTER(
		ALLSELECTED( Dates),
		Dates[Date] <= MAX( Dates[Date])
	)
)
//use ALLSELECTED to only bring in dates that you have actually selected

Cumulative Sales 2 Years Prior = 
CALCULATE( 
	[Sales 2 Yrs Prior] , 
	FILTER(
		ALLSELECTED( Dates ),
		Dates[Date] <= MAX( Dates[Date])
	)
)


Cumulative Sales LY = 
CALCULATE( 
	[Sales LY] , 
	FILTER(
		ALLSELECTED( Dates ),
		Dates[Date] <= MAX( Dates[Date])
	)
)


Sales LY = 
CALCULATE( 
	[Total Sales] , 
		SAMEPERIODLASTYEAR( Dates[Date] ))


Sales 2 Yrs Prior = 
CALCULATE( 
	[Total Sales] , 
		SAMEPERIODLASTYEAR(
			SAMEPERIODLASTYEAR( Dates[Date] )))

//retrieve sales from 2 year prior

6

Many thanks, C.

7

Hi @ammu, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

1 Like
8

Ammu you can you try this:

LY Actual = Calculate( [CY Actual] , Dateadd [date] , -1 , Year)

1 Like