Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
vytas
Helper I
Helper I

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 :

SaldoInvoiceDateDocumentNrCompanyNoPaymentDay
117.092020-02-1811258872020-05-28
44.042020-02-0521258872020-05-15
50.322020-04-2731258872020-01-08
90.052020-03-3141258872020-03-09
202.032020-04-2751258872020-01-14
442.252020-04-1661258872020-05-16
574.032020-04-1671258872020-05-16
167.672020-04-1681258872020-05-16
133.022020-04-1691258872020-05-16
110.22020-04-17101258872020-05-17
51.032020-04-20111258872020-05-20
149.762020-04-20121258872020-05-20
500.342020-04-20131258872020-05-20
45.512020-04-22141258872020-05-22
102.742020-04-22151258872020-05-22
102.532020-04-22161258872020-05-22
71.392020-04-22171258872020-05-22
67.272020-04-22181258872020-05-22
56.612020-04-23191258872020-05-23
169.822020-04-23201258872020-05-23
177.882020-04-23211258872020-05-23
1842020-04-23221258872020-05-23
34.252020-04-24231258872020-05-24
70.62020-04-28241258872020-05-28

 

How could i write a measure which gives me difference in days between max and min invoice date

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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

 

View solution in original post

3 REPLIES 3
Mariusz
Community Champion
Community Champion

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

 

 

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

Just wrapped RETURN in datediff.

 

Thank You. 

 

amitchandak
Super User
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...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.