datediff calculated dates from segmented filter

Question

Hi,&nbsp;-I've this table:&nbsp;Code &nbsp; &nbsp; values &nbsp; &nbsp; duedate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; debit &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; creditC123 &nbsp; &nbsp; 125 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-01-2017 &nbsp; &nbsp; &nbsp; &nbsp;32 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2C231 &nbsp; &nbsp; 235 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-01-2017 &nbsp; &nbsp; &nbsp; &nbsp;25 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20&nbsp;-other table with Calendar Date&nbsp;When I choose a dd-mm-yyyy from calendar date (segmented filter), I want a calculated measure like that:&nbsp;for example: "0 to 10 days"&nbsp;Measure = 
IF(AND( DATEDIFF( Allselected(Consulta1[DueDate] ); Allselected(Calendar[date] ) ;DAY) &gt;0 ; DATEDIFF(allselected( Consulta1[DueDate] ); Allselected(Calendar[date] ) ; day ) &lt;= 10 ); SUM(Consulta1[Debit]) - sum(Consulta1[Credit]); 0 )I can't get the correct values, can anyone help me?&nbsp;

anonymous · Answer

Hi,&nbsp;In other words.. I've this query from SAP B1, and I want to find a solution to my variables&nbsp;[%1]. I think the better&nbsp;approach is to select a date from a table and substitute the [%1] by the date I selected.&nbsp;If I substitute the [%1] manually in my query I get the correct values, but it isn't a solution substitute the date everytime I need a new result.&nbsp;My first approach was removing the "cases" and create a measure, but&nbsp;unsuccessful.&nbsp;If anyone can help me I&nbsp;will appreciate.&nbsp;&nbsp;SELECT T1."CardCode", T1."CardName", T1."CreditLine", T0."RefDate", T0."Ref1" "Document Number",
   CASE  WHEN T0."TransType"=13 THEN 'Invoice'
    WHEN T0."TransType"=14 THEN 'Credit Note'
    WHEN T0."TransType"=30 THEN 'Journal'
    WHEN T0."TransType"=24 THEN 'Receipt'
    END AS "Document Type"
      ,(T0."Debit"- T0."Credit"-COALESCE(T5."ReconSum",0)) "Balance"
      ,(CASE WHEN datediff(day,T0."DueDate",[%1])&lt;=0 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "Future"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])&gt;0 AND datediff(day,T0."DueDate",[%1])&lt;=30 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "Current"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])&gt;30 AND datediff(day,T0."DueDate",[%1])&lt;=60 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "31-60 Days"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])&gt;60 AND datediff(day,T0."DueDate",[%1])&lt;=90 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "61-90 Days"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])&gt;90 AND datediff(day,T0."DueDate",[%1])&lt;=120 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "91-120 Days"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])&gt;120 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "121+ Days"
FROM JDT1 T0
INNER JOIN OCRD T1 ON T0."ShortName" = T1."CardCode"
INNER JOIN OJDT T2 ON T0."TransId" = T2."TransId"
LEFT JOIN (
 SELECT T4."ShortName", T4."TransId", SUM( T4."ReconSum" * CASE WHEN T4."IsCredit" = 'D' THEN 1 ELSE -1 END ) AS "ReconSum"
 FROM OITR T3
 INNER JOIN ITR1 T4 ON T3."ReconNum" = T4."ReconNum"
 WHERE T3."ReconDate" &lt;= [%1]
 GROUP BY T4."ShortName", T4."TransId"
) T5 ON T0."TransId" = T5."TransId" AND T0."ShortName" = T5."ShortName"
WHERE T0."RefDate" &lt;= [%1] AND T1."CardType" = 'C'
AND (T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0)) &lt;&gt; 0
ORDER BY T1."CardCode", T0."DueDate", T0."Ref1"&nbsp;

v-caliao-msft · Answer

Hi Anonymous,
&nbsp;
In your sample data,
Code &nbsp; &nbsp; values &nbsp; &nbsp; duedate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; debit &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; credit
C123 &nbsp; &nbsp; 125 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-01-2017 &nbsp; &nbsp; &nbsp; &nbsp;32 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2
C231 &nbsp; &nbsp; 235 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-01-2017 &nbsp; &nbsp; &nbsp; &nbsp;25 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20
If 02-01-2017 is selected in your fiter, which value do you do to show on your report?
&nbsp;
Regards,
Charlie Liao

anonymous · Answer

v-caliao-msft, sorry for the delay, this is the closest query for aging report in SAP. In this scenario, if you choose 02-01-2017 I will get the following:&nbsp;Code &nbsp; &nbsp; values &nbsp; &nbsp; duedate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; debit &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; credit &nbsp; &nbsp; 0-10 &nbsp; &nbsp;11-20 &nbsp; &nbsp; 21-30 &nbsp; &nbsp; 31-40 &nbsp; &nbsp; &nbsp; 41+C123 &nbsp; &nbsp; 125 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-01-2017 &nbsp; &nbsp; &nbsp; &nbsp;32 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;30 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp;C231 &nbsp; &nbsp; 235 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-01-2017 &nbsp; &nbsp; &nbsp; &nbsp;25 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp;We do the following calculations, if (01-jan-2017) - (02-jan-2017) = 1 day, so we only have results to the 0-10 column/measure&nbsp;with the 05-fev-2017 date:&nbsp;Code &nbsp; &nbsp; values &nbsp; &nbsp; duedate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; debit &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; credit &nbsp; &nbsp; 0-10 &nbsp; &nbsp;11-20 &nbsp; &nbsp; 21-30 &nbsp; &nbsp; 31-40 &nbsp; &nbsp; &nbsp; 41+C123 &nbsp; &nbsp; 125 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-01-2015&nbsp; &nbsp; &nbsp; &nbsp; 10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp;C231 &nbsp; &nbsp; 235 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;01-01-2017 &nbsp; &nbsp; &nbsp; &nbsp;25 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0

Forum Discussion

datediff calculated dates from segmented filter

3 Replies

Related content

DATEDIFF DAX

Calculate Based on Filtered Segment

Calculate segment

DATEDIFF incorrectly calculating

Datediff Calculation