Forum Discussion
DAX Query for calculated column which refers the previous month end data
Below is my avaiable dataset. My dataset contains the product details with date field with has all the month end dates alone.
I need to create the below highlighted calculated column "Previous Amount" which takes the amount field of the previous month end date and populates.
How to do it ? Product Name and Facility Type are combined to be unique identifier.
Anonymous , for a new column
New column =
var _prd = [product name]
var _typ = [facility Type]
var _pd = [previous date]
return
sumx(filter(Table, [product name] = _prd && [facility Type] =_typ && [Date] =_pd), Amount)For measure use date table and create measures like
MTD Sales = CALCULATE(SUM(Table[Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Table[Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
1 Reply
- amitchandak
Super User
Anonymous , for a new column
New column =
var _prd = [product name]
var _typ = [facility Type]
var _pd = [previous date]
return
sumx(filter(Table, [product name] = _prd && [facility Type] =_typ && [Date] =_pd), Amount)For measure use date table and create measures like
MTD Sales = CALCULATE(SUM(Table[Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Table[Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))