Forum Discussion

JavidRobatian's avatar
JavidRobatian
Frequent Visitor
2 years ago
Solved

DATEDIFF with condition

Hi experts, hope you all doing good.
I have two tables in Power BI called Date and Invoice that are related with (invoice[date] 1:* Date[Date])
Now I'm trying to Calculate DateDiff with below condition:
1- The user not use slicer, in this case:
DateDiff = date[invoice] - today()
2- The user select the month using the table:
For example if user select 11 in month slicer:
DateDiff = date[invoice] - 11/01/2023
thank you help me to Calculate DateDiff with up condition.

  • Dangar332's avatar
    Dangar332
    2 years ago

    Hi, JavidRobatian 

    try below

    Measure = 
var a = SELECTEDVALUE('date'[month])
var b = DATEDIFF(CALCULATE(min(invoice[date]),ALLEXCEPT(invoice,invoice[cu nu])),TODAY(),DAY)
var c = DATEDIFF(CALCULATE(min(invoice[date]),ALLEXCEPT(invoice,invoice[cu nu])),DATE(2023,a,1),DAY)
var d = IF(ISFILTERED('date'[month]),c,b)
return
d

12 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    2 years ago

    JavidRobatian 

    Try this measure: Make sure you add a Year slicer

     

    Datediff = 
    IF(
		ISFILTERED( 'Date'[Month] ) , 
		INT( MAX( 'Date'[Date] )  - MAX( Invoice[Date] ) ),
		INT(TODAY() -   MAX( Invoice[Date] ) )
	)

     

    • JavidRobatian's avatar
      JavidRobatian
      Frequent Visitor
      2 years ago

      Thank you for your reply
      I tried this but I think it doesn't work properly.

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    Hi, JavidRobatian 

    try below

    Measure = 
var a = SELECTEDVALUE('date'[month])
var b = DATEDIFF(CALCULATE(min(invoice[date]),ALLEXCEPT(invoice,invoice[cu nu])),TODAY(),DAY)
var c = DATEDIFF(CALCULATE(min(invoice[date]),ALLEXCEPT(invoice,invoice[cu nu])),DATE(2023,a,1),DAY)
var d = IF(ISFILTERED('date'[month]),c,b)
return
d
    • JavidRobatian's avatar
      JavidRobatian
      Frequent Visitor
      2 years ago

      Thank you for your reply.
      I tried this but its partly works.
      I changed Min to Max

      its work well when I not use slicer.

      but I want when I choose 10 (in month slicer) Datediff equal to:

      and when I choose 11 (in month slicer) Datediff equal to:

       

      • Dangar332's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        HI, JavidRobatian 

        in your previous post DATEDIFF between DATE column and User SELECT Date 

        you mention smallest number for each customer thats why i use min if you want latest date then you can use max()

        with same formula select month(11) below result in image 

         

         

        with same formula select month(10) below result in image 

         

        in above output is some diff in output

        beacuse i am take your previous post data