cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User III
Super User III

Re: Return Date day beetween Penultimate an last Purchase record

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 Datanaut!

Check out my blog:

Power BI em Português





Microsoft
Microsoft

Re: Return Date day beetween Penultimate an last Purchase record


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

wagnerantunes84
New Member

Re: Return Date day beetween Penultimate an last Purchase record

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!

 

 

 

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors