Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
johnmelbourne
Helper V
Helper V

Compare most recent June to previous June

Data Table
  
DateValue
30-Jun-1910
1-Jun-2015

 

I have month level granularity data and want to compare the most recent June to the previous June. The date is different from 2019 to 2020.

 

Any ideas how I might achieve this?

 

 

Any ideas what function I should be looking at? 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@johnmelbourne , if have date, you can use time intelligence date table and a slicer to do that

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

 

Other wise you have hardcode meeasures

This jun =
var _min = date(year(today()),6,1))
var _max = eomonth(_min,0)
return
calculate(Sum(Table[Value]), filter(all(Date),Date[Date]>= _min && Date[Date]<=_max)) //Remove all from date if needed

last jun =
var _min = date(year(today())-1,6,1))
var _max = eomonth(_min,0)
return
calculate(Sum(Table[Value]), filter(all(Date),Date[Date]>= _min && Date[Date]<=_max)) //Remove all from date if needed

 

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.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@johnmelbourne , if have date, you can use time intelligence date table and a slicer to do that

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

 

Other wise you have hardcode meeasures

This jun =
var _min = date(year(today()),6,1))
var _max = eomonth(_min,0)
return
calculate(Sum(Table[Value]), filter(all(Date),Date[Date]>= _min && Date[Date]<=_max)) //Remove all from date if needed

last jun =
var _min = date(year(today())-1,6,1))
var _max = eomonth(_min,0)
return
calculate(Sum(Table[Value]), filter(all(Date),Date[Date]>= _min && Date[Date]<=_max)) //Remove all from date if needed

 

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.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

 

Wow. You are seriously good. I should have said I have a date table and will mention in any future posts (I know there will be another one one day). Sincere thanks for going to the trouble and especially all that trouble for the non-date calendar code.

I was trying EOMONTH, CLOSINGBALANCEYEAR, Filtering by MAX year and month = "June" and failing everywhere to get what I wanted. 

 

You are amazing.

Kind regards

John

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.