Previous Date value based on the date selection

[Anonymous]

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 

Team	Value
X	2
Y	5
Z	1

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

[Anonymous · Accepted solution]

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

[Syndicate_Admin]

There @Syndicate_Admin ,

Thank you! I just removed all relations and recreated. Now its working.

[amitchandak]

@Anonymous , You should prefer date table. if two date are selected , measure like this for min date

new measure =
var _max = minx(allselected(Date),Date[Date])
return
calculate( SUM('TransactionTable'[Value), filter('Date', 'Date'[Date] =_min ))

For the last date the previous date refer

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

[Anonymous]

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]

@Anonymous , Then try

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

[Anonymous]

@amitchandak Thank you for the reply. I have tried that with maxdate-1. Still it is  showing the totals only not the values.

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

Team	Value
X	2
Y	5
Z	1

I need the output by the Team name like above but im getting  the resuts as  below with only totals not the values.

Team	Value
X
Y
Z
Total	8

Can anyone please help me out?

Thanks in advance,

Vishnu Priya

[ryan_mayu]

@Anonymous 

pls see the attachment below