get last 6 months last day value

[Anonymous]

Hi All, @Anonymous @Anonymous @Greg_Deckler @amitchandak @nandic 

Need you help with a DAX formula .

I would need to derive the last 6 months AGO last day sales . Please note the requirement is to get data for  last date of 6 month ago  and not for the entire 6 months from selected date. E.g. if today is Dec 31 2020 , then i would need to get data for 30 jun 2020 (last non blank value of month of Jun 2020) and compare against Dec 31 2020.

i do have a calendar table which has month offset but am not able to get it working . 

i have used DATEADD FUNCTION = DATEADD(CALENDAR[EOM],-6,MONTH) but this works for some months and not for others .

appreciate your help . 

i have tried the below as well , but this dosen't seem to work for some of th emonths 

var last6month = values('Calendar'[last6month]) // this is a custom column created in calendar table to get last date of 6 month ago 

Var SALES6Mago =

SUMX(FILTER(ALL('Calendar'),'Calendar'[Date]=lastmonth),[Total SALES])

here is a sample data :

Date	Product	Price
29 Jun 2020	A	1
29 Jun 2020	B	2
30 Jun 2020	A	3
30 Jun 2020	B	4
30 Dec 2020	A	5
30 Dec 2020	B	6
31 Dec 2020	A	7
31 Dec 2020	B	8

Expected Result is 

Today = 31 Dec 2020 = SUMX(7+8) = 15
last 6 month ago = 30 JUn = SUMX(3+4) = 7 
Thanks in advance

[Tahreem24]

@Anonymous  - You can calculate last day of 6 month ago price with below measure:

Latest 6 Month Price  = CALCULATE(SUM(TableA[Price]),DATEADD(ENDOFMONTH(TableA[FinalDate]),-6,MONTH))

[Anonymous]

Hi,

have you tryed the function: EOMONTH(date, -6) ?

[Anonymous]

Sorry @Anonymous  this dosen't work, it goes back 6 days from last month date 

[Anonymous]

Thanks @amitchandak  i have tried this formulas but these (DateADD) doesn't work . THanks for your help . finally i ended up crearting last 6 months , last 12 months date based on calendar date and this seems to be working . 

[amitchandak · Accepted solution]

@Anonymous , you time intelligence with date table

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

last 6 MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-6,MONTH)))

6 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-6,Month))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA