Forum Discussion
Solved
Min/Max Dates
Hi,
I have virtual table :
Unpaid Invoices =
FILTER (
ADDCOLUMNS (
SUMMARIZE (
Debt;
Debt[DocumentNr];
Debt[CompanyNo];
Debt[PaymentDay]
);
"Saldo"; CALCULATE ( SUM ( Debt[Debt] ) );
"InvoiceDate"; CALCULATE ( MAX ( Debt[InvoiceDate] ) )
);
[Saldo] > 0
)
Result is :
| Saldo | InvoiceDate | DocumentNr | CompanyNo | PaymentDay |
| 117.09 | 2020-02-18 | 1 | 125887 | 2020-05-28 |
| 44.04 | 2020-02-05 | 2 | 125887 | 2020-05-15 |
| 50.32 | 2020-04-27 | 3 | 125887 | 2020-01-08 |
| 90.05 | 2020-03-31 | 4 | 125887 | 2020-03-09 |
| 202.03 | 2020-04-27 | 5 | 125887 | 2020-01-14 |
| 442.25 | 2020-04-16 | 6 | 125887 | 2020-05-16 |
| 574.03 | 2020-04-16 | 7 | 125887 | 2020-05-16 |
| 167.67 | 2020-04-16 | 8 | 125887 | 2020-05-16 |
| 133.02 | 2020-04-16 | 9 | 125887 | 2020-05-16 |
| 110.2 | 2020-04-17 | 10 | 125887 | 2020-05-17 |
| 51.03 | 2020-04-20 | 11 | 125887 | 2020-05-20 |
| 149.76 | 2020-04-20 | 12 | 125887 | 2020-05-20 |
| 500.34 | 2020-04-20 | 13 | 125887 | 2020-05-20 |
| 45.51 | 2020-04-22 | 14 | 125887 | 2020-05-22 |
| 102.74 | 2020-04-22 | 15 | 125887 | 2020-05-22 |
| 102.53 | 2020-04-22 | 16 | 125887 | 2020-05-22 |
| 71.39 | 2020-04-22 | 17 | 125887 | 2020-05-22 |
| 67.27 | 2020-04-22 | 18 | 125887 | 2020-05-22 |
| 56.61 | 2020-04-23 | 19 | 125887 | 2020-05-23 |
| 169.82 | 2020-04-23 | 20 | 125887 | 2020-05-23 |
| 177.88 | 2020-04-23 | 21 | 125887 | 2020-05-23 |
| 184 | 2020-04-23 | 22 | 125887 | 2020-05-23 |
| 34.25 | 2020-04-24 | 23 | 125887 | 2020-05-24 |
| 70.6 | 2020-04-28 | 24 | 125887 | 2020-05-28 |
How could i write a measure which gives me difference in days between max and min invoice date
Hi vytas
Try something like this.
Measure = VAR __tbl = FILTER ( ADDCOLUMNS ( SUMMARIZE ( Debt; Debt[DocumentNr]; Debt[CompanyNo]; Debt[PaymentDay] ); "Saldo"; CALCULATE ( SUM ( Debt[Debt] ) ); "InvoiceDate"; CALCULATE ( MAX ( Debt[InvoiceDate] ) ) ); [Saldo] > 0 ) VAR __min = MINX( __tbl, [InvoiceDate] ) VAR __max = MAXX( __tbl, [InvoiceDate] ) RETURN __max - __minBest Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
3 Replies
vytas , you can get
Datediff(Min(Table[Date]),Max(Table[Date]),Day)
Refer this blog how to use row context to get correct date diff
Hi vytas
Try something like this.
Measure = VAR __tbl = FILTER ( ADDCOLUMNS ( SUMMARIZE ( Debt; Debt[DocumentNr]; Debt[CompanyNo]; Debt[PaymentDay] ); "Saldo"; CALCULATE ( SUM ( Debt[Debt] ) ); "InvoiceDate"; CALCULATE ( MAX ( Debt[InvoiceDate] ) ) ); [Saldo] > 0 ) VAR __min = MINX( __tbl, [InvoiceDate] ) VAR __max = MAXX( __tbl, [InvoiceDate] ) RETURN __max - __minBest Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
