Forum Discussion

StaceyG's avatar
StaceyG
Icon for Helper I rankHelper I
4 years ago
Solved

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

  • 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's avatar
      StaceyG
      Icon for Helper I rankHelper 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?