Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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
@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
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |