datediff calculated dates from segmented filter

[Anonymous]

Hi,

-I've this table:

Code     values     duedate            debit           credit

C123     125          01-01-2017        32                2

C231     235          01-01-2017        25                20

-other table with Calendar Date

When I choose a dd-mm-yyyy from calendar date (segmented filter), I want a calculated measure like that:

for example: "0 to 10 days"

Measure = 
IF(AND( DATEDIFF( Allselected(Consulta1[DueDate] ); Allselected(Calendar[date] ) ;DAY) >0 ; DATEDIFF(allselected( Consulta1[DueDate] ); Allselected(Calendar[date] ) ; day ) <= 10 ); SUM(Consulta1[Debit]) - sum(Consulta1[Credit]); 0 )

I can't get the correct values, can anyone help me?

[v-caliao-msft]

Hi @Anonymous,

In your sample data,

Code     values     duedate            debit           credit

C123     125          01-01-2017        32                2

C231     235          01-01-2017        25                20

If 02-01-2017 is selected in your fiter, which value do you do to show on your report?

Regards,

Charlie Liao

[Anonymous]

@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:

Code     values     duedate            debit           credit     0-10    11-20     21-30     31-40       41+

C123     125          01-01-2017        32                2              30               0            0              0           0 

C231     235          01-01-2017        25                20              5               0            0              0           0

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

with the 05-fev-2017 date:

Code     values     duedate            debit           credit     0-10    11-20     21-30     31-40       41+

C123     125          01-01-2015        10                2                0               0            0              0           8 

C231     235          01-01-2017        25                20              0               0            0              5           0

[Anonymous]

Hi,

In other words.. I've this query from SAP B1, and I want to find a solution to my variables [%1]. I think the better approach is to select a date from a table and substitute the [%1] by the date I selected.

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.

My first approach was removing the "cases" and create a measure, but unsuccessful.

If anyone can help me I will appreciate.

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])<=0 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "Future"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])>0 AND datediff(day,T0."DueDate",[%1])<=30 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "Current"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])>30 AND datediff(day,T0."DueDate",[%1])<=60 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "31-60 Days"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])>60 AND datediff(day,T0."DueDate",[%1])<=90 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "61-90 Days"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])>90 AND datediff(day,T0."DueDate",[%1])<=120 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "91-120 Days"
   ,(CASE WHEN datediff(day,T0."DueDate",[%1])>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" <= [%1]
 GROUP BY T4."ShortName", T4."TransId"
) T5 ON T0."TransId" = T5."TransId" AND T0."ShortName" = T5."ShortName"
WHERE T0."RefDate" <= [%1] AND T1."CardType" = 'C'
AND (T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0)) <> 0
ORDER BY T1."CardCode", T0."DueDate", T0."Ref1"