Getting last 12 months total in a column to filter with date

[PoojaShri]

Hello there,

I am trying to get the last 12 months' total revenue in a calulcated column, which I can filter with a date slicer. I want a table at the end which shows selected month's revenue and last 12 months' revenue from the selected date.

How to calculate the same if the Dates are the end of each month? I tried below DAX for measure but when date filter is applied, the measure goes blank. The calculated column using DATESYTD gives the same number as Revenue (mentioned in the table below)

Sharing the sample data for reference:

Date	ID	Revenue	Revenue YTD (Column)	Revenue_Ytd (Measure)
31/07/2018	32466	5495.42	5495.42
31/08/2018	32466	2815.07	2815.07
30/09/2018	32466	2878.24	2878.24
31/10/2018	32466	2795.92	2795.92
30/11/2018	32466	2818.15	2818.15
31/12/2018	32466	2664.91	2664.91
31/01/2019	32466	2445.28	2445.28
28/02/2019	32466	2262.11	2262.11
31/03/2019	32466	2067.89	2067.89
30/04/2019	32466	2314.28	2314.28	28557.27
31/05/2019	32466	2261.89	2261.89	30819.16
30/06/2019	32466	2342.66	2342.66	33161.82
31/07/2019	32466	2260.47	2260.47	29926.87
31/08/2019	32466	2281.32	2281.32	29393.12
30/09/2019	32466	2305.82	2305.82	28820.7
31/10/2019	32466	2260.63	2260.63	28285.41
30/11/2019	32466	2333.88	2333.88	27801.14
31/12/2019	32466	2290.43	2290.43	27426.66
31/01/2020	32466	2354.21	2354.21	27335.59
28/02/2020	32466	2370.57	2370.57	27444.05
31/03/2020	32466	2186.49	2186.49	27562.65
30/04/2020	32466	2143.56	2143.56	27391.93
31/05/2020	32466	1987.85	1987.85	27117.89
30/06/2020	32466	2092.42	2092.42	26867.65
31/07/2020	32466	2036.24	2036.24	26643.42
31/08/2020	32466	2070.13	2070.13	26432.23
30/09/2020	32466	2090.24	2090.24	26216.65
31/10/2020	32466	1992.35	1992.35	25948.37
30/11/2020	32466	2046.59	2046.59	25661.08
31/12/2020	32466	2018.55	2018.55	25389.2
31/01/2021	32466	2076.07	2076.07	25111.06
28/02/2021	32466	2089.05	2089.05	24829.54
31/03/2021	32466	1870.88	1870.88	24513.93
1/04/2021	32466	2077.57	2077.57	24447.94

Expected result:

Date	ID	Revenue	Revenue_YTD
31/03/2021	32466	1870.88	24513.93

The DAX I used for measure is:

Revenue_Ytd (Measure) =

VAR LastNotBlankDate =

CALCULATE (

LASTDATE ( 'Client Details'[Date] ),

FILTER (

ALLSELECTED ('Client Details'),

LASTNONBLANK ( 'Client Details'[Date], 'Client Details'[Revenue.1] )))

VAR MinDate =

CALCULATE ( FIRSTDATE ( 'Client Details'[Date] ), ALLSELECTED ( 'Client Details' ) )

VAR Revenue_of_last_year =

IF (

MAX ( 'Client Details'[Date] ) <= LastNotBlankDate,

CALCULATE (

SUM ( 'Client Details'[Revenue.1] ),

DATESINPERIOD ('Client Details'[Date], LASTDATE ('Client Details'[Date]), -1, YEAR )

),

CALCULATE (

SUM ( 'Client Details'[Revenue.1] ),

DATESINPERIOD ('Client Details'[Date], LastNotBlankDate, -1, YEAR )

),

VAR CurrentRowDate =

MAX ( 'Client Details'[Date])

VAR Revenue_of_last_year_2 =

IF (

DATEDIFF ( MinDate, CurrentRowDate, MONTH ) + 1 < 12,

BLANK (),

Revenue_of_last_year)

RETURN

IF (

DATEDIFF ( MinDate, MAX ('Client Details'[Date] ), MONTH ) + 1 < 12,

BLANK (),

Revenue_of_last_year

)

Any help would be greatly appreciated!

Thanks!

@MFelix , @Pragati11 , @v-kelly-msft 

[amitchandak]

@PoojaShri , You can not use slicer value in a calculated column, You have to create a measure only

example with date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[PoojaShri · Accepted solution]

Hi @amitchandak ,

Thank you for the reply!

I resolved the issue by replacing "ALLSELECTED" with "ALL" in my mentioned measure (filter context at play!). And it works as expected with the date slicer.

The modified measure becomes:

Revenue_Ytd (Measure) =

VAR LastNotBlankDate =

CALCULATE (

LASTDATE ( 'Client Details'[Date] ),

FILTER (

ALL ('Client Details'),

LASTNONBLANK ( 'Client Details'[Date], 'Client Details'[Revenue.1] )))

VAR MinDate =

CALCULATE ( FIRSTDATE ( 'Client Details'[Date] ), ALL ( 'Client Details' ) )

VAR Revenue_of_last_year =

IF (

MAX ( 'Client Details'[Date] ) <= LastNotBlankDate,

CALCULATE (

SUM ( 'Client Details'[Revenue.1] ),

DATESINPERIOD ('Client Details'[Date], LASTDATE ('Client Details'[Date]), -1, YEAR )

),

CALCULATE (

SUM ( 'Client Details'[Revenue.1] ),

DATESINPERIOD ('Client Details'[Date], LastNotBlankDate, -1, YEAR )

),

VAR CurrentRowDate =

MAX ( 'Client Details'[Date])

VAR Revenue_of_last_year_2 =

IF (

DATEDIFF ( MinDate, CurrentRowDate, MONTH ) + 1 < 12,

BLANK (),

Revenue_of_last_year)

RETURN

IF (

DATEDIFF ( MinDate, MAX ('Client Details'[Date] ), MONTH ) + 1 < 12,

BLANK (),

Revenue_of_last_year

)