Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Previous Date value based on the date selection

Hi Everyone,

 

I'm trying to get the Previous Date's value for each Team. Below is the way I have relations in my model.  And in the dashboard, Date slicer is there (Dates Master Table). So, based on the selected date, I need to show  a table with the Maxdate selected-1 for each team.

 

 

 

If the Date range is from 03-01-2022 to 04-01-2022, my table  need to show the data related to 03-01-2022.

Desired way 

TeamValue
X2
Y5
Z1

 

 

I have tried  to use this below DAX. But it is giving me the totals as correct but the values as blanks.

 

Before Date values =
var x = CALCULATE(MAX('Dates'[Date]))
return
CALCULATE(SUM('TransactionTable'[Value),FILTER(all('TransactionTable All'),[Date] = (x-2)))
 
 
Can anyone please help me to get this?
 
Thank you in advance
Vishnu Priya
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Use independent date table as slicer and create a measure like below:

    Measure = 
var _previous = EDATE(MAX('Date'[date]),-1)
return
CALCULATE(SUM(TransactionTable[value]),FILTER(TransactionTable,TransactionTable[Date]=_previous))

    Or create a measure as below and add it to visual filter set value = 1:

    Measure 2 = 
var _previous = EDATE(MAX('Date'[date]),-1)
return
IF(SELECTEDVALUE('TransactionTable'[Date])=_previous,1,0)

     

    Best Regards,

    Jay

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you amitchandak  for the reply. I have  tried  this. And  I cant use MINX because I need  the previpus days data(If the date range is 2-01-2022 to 04-01-2022, I need the 3rd data. and if the range is from 02-01-2022 to 05-01-2022, I need 04-01-2022 data). So I have tried _max-1 in the filter context.

      Still, it is  giving  me the values in the table  as blanks and  totals are correct.

       

       

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        Anonymous , Then try

        new measure =
        var _max = maxx(allselected(Date),Date[Date]) -1
        return
        calculate( SUM('TransactionTable'[Value), filter('Date', 'Date'[Date] =_max ))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Use independent date table as slicer and create a measure like below:

    Measure = 
var _previous = EDATE(MAX('Date'[date]),-1)
return
CALCULATE(SUM(TransactionTable[value]),FILTER(TransactionTable,TransactionTable[Date]=_previous))

    Or create a measure as below and add it to visual filter set value = 1:

    Measure 2 = 
var _previous = EDATE(MAX('Date'[date]),-1)
return
IF(SELECTEDVALUE('TransactionTable'[Date])=_previous,1,0)

     

    Best Regards,

    Jay