relative date

[Anonymous]

Hello, 

I got an inventroy table like this, where I have the elaboration and expiry date for some products, I want to keep track of the number of expired products products and $ lost each week, I need your help in caclulating a measure to view expired products previous months and next months with a slicer, relative date

Thanks

[v-frfei-msft]

Hi @Anonymous,

Here I made one sample for your reference.

1. Enter the data and create a dimtime table.

dimtime = CALENDARAUTO()

2. Create the measures as below.

next month J = IF(MONTH(SELECTEDVALUE(dimtime[Date]))<>12, IF(MAX(Table1[exp_date])<=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),MONTH(SELECTEDVALUE(dimtime[Date]))+1,31) && MAX(Table1[exp_date])>=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),MONTH(SELECTEDVALUE(dimtime[Date]))+1,1),1,0),IF(MAX(Table1[exp_date])<=DATE(YEAR(SELECTEDVALUE(dimtime[Date]))+1,1,31) && MAX(Table1[exp_date])>=DATE(YEAR(SELECTEDVALUE(dimtime[Date]))-1,1,1),1,0))

Previous month J = IF(MONTH(SELECTEDVALUE(dimtime[Date]))<>1, IF(MAX(Table1[exp_date])<=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),MONTH(SELECTEDVALUE(dimtime[Date]))-1,31) && MAX(Table1[exp_date])>=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),MONTH(SELECTEDVALUE(dimtime[Date]))-1,1),1,0),IF(MAX(Table1[exp_date])<=DATE(YEAR(SELECTEDVALUE(dimtime[Date]))-1,12,31) && MAX(Table1[exp_date])>=DATE(YEAR(SELECTEDVALUE(dimtime[Date])),12,1),1,0))

TOTAL AMOUNT = CALCULATE(MAX(Table1[cost])*MAX(Table1[amount]))

3. Create visuals and make the tables visuals filterd by [next month J] and [Previous month J] seperatly. Then we can get the result as we excepted.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/4wckydml9xe5bbx/relative%20date.pbix?dl=0

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,

Frank

[Anonymous]

Thanks @v-frfei-msft but I think that I need a different aproach cause it didn't worked for me.

If there is another waty to do so I will appreciatte

Regards 

Christian