Min/Max Dates

[vytas]

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

[Mariusz · Accepted solution]

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 - __min 

Best Regards,
Mariusz

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

Please feel free to connect with me.
LinkedIn

[vytas]

@Mariusz 

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

Just wrapped RETURN in datediff.

Thank You. 

[amitchandak]

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