How to use Datediff in measure

[Anonymous]

Material	Date	Quantity
a	12/08/2021	152
a	13/08/2021	748
b	14/08/2021	631
b	15/08/2021	421
b	16/08/2021	17
c	17/08/2021	325
c	18/08/2021	-793
c	19/08/2021	-20
e	15/10/2021	-40
e	16/10/2021	-451
e	17/10/2021	-179
b	18/10/2021	832
a	19/10/2021	1287
a	20/10/2021	-23
a	21/10/2021	17
c	22/10/2021	37
c	23/10/2021	-76
c	24/10/2021	37

I want to create a measure to create an if else statement to find the values where Quantity is negative and the week difference between today and Date column is less than or equal to 6. I can create a column using the following measure: This works but creates duplications when I use it in matrix visual.

Stock = MINX(data,data[Quantity])
Date Diff = WEEKNUM(data[Date])-WEEKNUM(TODAY())
Short 6 Weeks Column = IF([Stock]<=0 && (data[Date Diff] >=0 && data[Date Diff]<=6),1,0)

[v-stephen-msft]

Hi @Anonymous ,

Try to use DATEDIFF function, the measure is

Measure = IF(DATEDIFF(TODAY(),MAX('Table'[Date ]),WEEK)<=6&&MAX('Table'[Quantity])<0,1,0)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Anonymous , Try measure like

measure =
var _1= calculate(MIN(data[Quantity]), filter(allselected(data), date[Material] = max(Data[Material]) && datediff(data[Date], today(), day)/7 <=6 ))
return
if(isblank(_1),1,0)

[Anonymous]

Hi @amitchandak this is working but the problem is that it always starts from the first date of Date column chich varies based on our slicers values. I want the measure to start counting from today and 6 weeks a head all the time. For example, if the date starts from two weeks a head from now, then this measure will show me all values 6 weeks starting from the two weeks, which is wrong. I want to show the first 6 weeks of time at all time if possible.

[Kumail]

Hello @Anonymous

If you could send sample .pbix that demonstrate what you are looking to get. It would really help providing you a quick solution.
 
You can send the sample .pbix file by adding it to your drive or dropbox and add the link here. 
 
Regards
Kumail Raza