Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Data Table | |
Date | Value |
30-Jun-19 | 10 |
1-Jun-20 | 15 |
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?
Solved! Go to Solution.
@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.
@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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |