Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Creating a Measured Column based on DATEDIFF

I am currently learning how to use Power BI and am trying to build a table and visual for on-time deliveries for our warehouse staff. The formula I used was: 

 

Days_Past_Required_Date =
VAR _Age =
            DATEDIFF(p21_view_oe_hdr[requested_date], p21_view_invoice_hdr[invoice_date], DAY) VAR _Result
= SWITCH(
TRUE(),
    _Age < 0, "Late",
    _Age >= 0, "On-Time"
)
Return
_Result

 

 

I have attached the error I am receiving as well as the dataset I am using. Are there any problems with this formula that could be giving me this error?

 

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Anonymous 

     

    It seems that the code is fine, but it is indeed the relationship that causes the context of the measure to include all possibilities, which leads to an increase in the number of rows. 

    Have you considered directly subtracting two dates?

    Like this:

    Days_Past_Required_Date =
VAR _Age =
    SELECTEDVALUE ( p21_view_oe_hdr[requested_date] )
        - SELECTEDVALUE ( p21_view_invoice_hdr[invoice_date] )
VAR _Result =
    SWITCH ( TRUE (), _Age < 0, "Late", _Age >= 0, "On-Time" )
RETURN
    _Result

    It is impossible for Datediff to return a result of <0.

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.
     
    Best Regards,
    Community Support Team _ Janey

6 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    Anonymous  try this

    Days_Past_Required_Date =
VAR _Age =
    DATEDIFF (
        MAX ( p21_view_oe_hdr[requested_date] ),
        MAX ( p21_view_invoice_hdr[invoice_date] ),
        DAY
    )
VAR _Result =
    SWITCH ( TRUE (), _Age < 0, "Late", _Age >= 0, "On-Time" )
RETURN
    _Result
    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      Anonymous  did you try this yet?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Sorry, I was out of the office. I just tried it and it gives me a new column that looks correct on the invoice side, but now shows the same order set with tons of separate invoices attached to it. I am not sure why this would be happening. Could it be because of the relationship between the two different tables I have to pull from with the invoice_no and order_no? Here is a picture of the issue.