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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wagnerantunes84
New Member

Return Date day beetween Penultimate an last Purchase record

I'm setting up a panel in PowerBI, where I have to identify activation! I have a Fat table with the customer / vendor columns / date sale / value: The same is already linked to a date table, I would like to calculate how many days runs between the last and last but one last sale by customers, in my case all dates are In a single column sale date. Could someone help me in the formula?

 

wee-01.jpg

3 REPLIES 3
v-ljerr-msft
Employee
Employee


I would like to calculate how many days runs between the last and last but one last sale by customers, in my case all dates are In a single column sale date. 

@MFelix, based on my understanding, ALLEXCEPT ( Table1, Table1[NameCliente] ) may be needed to calculate sales dates for each customer in this scenario. Smiley Happy

@wagnerantunes84, could your try using the formula below to create a measure to see if it works?

Previous_Sales_Days =
VAR lastPurchaseDate =
    CALCULATE (
        MAX ( Table1[DataBase] ),
        ALLEXCEPT ( Table1, Table1[NameCliente] )
    )
VAR penultimateDate =
    MAXX (
        FILTER (
            ALLEXCEPT ( Table1, Table1[NameCliente] ),
            Table1[DataBase] < Current_Date
        ),
        Sales_dates[DataBase]
    )
RETURN
    DATEDIFF ( Previous_Date, Current_Date, DAY )

Just replace Table1 with your real table name.Smiley Happy

 

Regards

Hi my friend, this firt method dont work. but i am trying this new example ok,

I'll get back to you soon, thank you very much.

 

sory my english, i am from brazil!

 

 

 

 

 

MFelix
Super User
Super User

Hi @wagnerantunes84,

 

Add this measure to your table:

 

Previous_Sales_Days = 
VAR Current_Date =
    MAX ( Sales_dates[DataBase] )
VAR Previous_Date =
    MAXX (
        FILTER ( ALL ( Sales_dates ); Sales_dates[DataBase] < Current_Date );
        Sales_dates[DataBase]
    )
RETURN
    DATEDIFF ( Previous_Date; Current_Date; DAY )

 

Regards,

MFelix

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.