Forum Discussion
Find max date less than slicer selected date
I feel like this should be really easy, but have spent many hours trying every possible solution I've seen, and none have worked.
I have a date table including Date, MonthEndDate, OneMonthPrior, PriorMonthEndDate. I have a Transactions table with numerous fact fields, Date, MontheEndDate.
I have Date[Date] related to Transactions[Date], 1 to many, single direction
I have a slicer for Date[MonthEndDate]
I have a table with SWITCH measure for selected month which begins like:
VAR MonthEndDate = SELECTEDVALUE('Date'[MonthEndDate])
VAR TotalTrx = CALCULATE(SUM(Transactions[Total Trx]), FILTER('Transactions', 'Transactions'[MonthEndDate]=MonthEndDate))
I need a second column which is the month prior to the selected month. I am struggling with the syntax... how do I calculate in the SWITCH measure for where the Transactions[MonthEndDate] = Date[PriorMonthEndDate] based on the selected Date[MonthEndDate}?
Thanks!
StaceyG , try like
VAR PriorMonthEndDate = eomonth(SELECTEDVALUE('Date'[MonthEndDate])-1)
VAR TotalTrx = CALCULATE(SUM(Transactions[Total Trx]), FILTER('Transactions', 'Transactions'[MonthEndDate]=PriorMonthEndDate ))selectvalue will work in measure, it will not work in new column
2 Replies
- amitchandak
Super User
StaceyG , try like
VAR PriorMonthEndDate = eomonth(SELECTEDVALUE('Date'[MonthEndDate])-1)
VAR TotalTrx = CALCULATE(SUM(Transactions[Total Trx]), FILTER('Transactions', 'Transactions'[MonthEndDate]=PriorMonthEndDate ))selectvalue will work in measure, it will not work in new column
- StaceyG
Helper I
amitchandak thank you for this suggestion! It certainly seems like it should work, but when I applied it to my scenario it did not. I don't get an error with the measure, but when inserted in the table, the column is blank. Any idea why?