cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PoojaShri
Resolver II
Resolver II

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

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

IDRevenueRevenue YTD (Column)Revenue_Ytd (Measure)

31/07/2018

324665495.425495.42 
31/08/2018324662815.072815.07 
30/09/2018324662878.242878.24 
31/10/2018324662795.922795.92 
30/11/2018324662818.152818.15 
31/12/2018324662664.912664.91 
31/01/2019324662445.282445.28 
28/02/2019324662262.112262.11 
31/03/2019324662067.892067.89 
30/04/2019324662314.282314.2828557.27
31/05/2019324662261.892261.8930819.16
30/06/2019324662342.662342.6633161.82
31/07/2019324662260.472260.4729926.87
31/08/2019324662281.322281.3229393.12
30/09/2019324662305.822305.8228820.7
31/10/2019324662260.632260.6328285.41
30/11/2019324662333.882333.8827801.14
31/12/2019324662290.432290.4327426.66
31/01/2020324662354.212354.2127335.59
28/02/2020324662370.572370.5727444.05
31/03/2020324662186.492186.4927562.65
30/04/2020324662143.562143.5627391.93
31/05/2020324661987.851987.8527117.89
30/06/2020324662092.422092.4226867.65
31/07/2020324662036.242036.2426643.42
31/08/2020324662070.132070.1326432.23
30/09/2020324662090.242090.2426216.65
31/10/2020324661992.351992.3525948.37
30/11/2020324662046.592046.5925661.08
31/12/2020324662018.552018.5525389.2
31/01/2021324662076.072076.0725111.06
28/02/2021324662089.052089.0524829.54
31/03/2021324661870.881870.8824513.93
1/04/2021324662077.572077.5724447.94

 

Expected result:

DateIDRevenueRevenue_YTD
31/03/2021324661870.8824513.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!
 
1 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
)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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
)

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors