cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Min/Max Dates

Hi,

I have virtual table :

Unpaid Invoices =

FILTER (

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

1 ACCEPTED SOLUTION
Community Champion

Hi @vytas

Try something like this.

``````Measure =
VAR __tbl =
FILTER (
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 - __min ``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

3 REPLIES 3
Community Champion

Hi @vytas

Try something like this.

``````Measure =
VAR __tbl =
FILTER (
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 - __min ``````

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

Helper I

That's what i needed. Strugled to reach date in virtual table.

Just wrapped RETURN in datediff.

Thank You.

Super User

@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

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.