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
Anonymous
Not applicable

Array formulae in Power BI for IF and MAX functions

Hi,

 

Appreciate it if anyone could help me out with this (simple) task? 

 

I am trying to replicate an exercise that I did on Excel, on Power BI. 

 

The task can be broken down to two parts; 

1) Last visit date-> Check if the current cell match with the data on column 'Name', if so, identify the penultimate date from column 'Order Date'

2) Days since last visit-> Calculate the difference between the current row's 'Order Date' and above penultimate 'Order Date'

 

I am sure this is a simple task but I could not get my DAX formulae to work properly. 

 

My excel formulae for the two tasks are as follows;

1) Last visit date-> {=MAX(IF([Name]=[@Name],([Order Date]<[@[Order Date]])*[Order Date],""))}

2) Days since last visit-> {(=IF([@[Order Date]]-[@[Last visit date]]<1000,[@[Order Date]]-[@[Last visit date]],"")} 

 

Appreciate it if anyone could help a DAX newbie. 🙂

 

Thanks,

Fonzy.
Capture.JPG

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

You may add calculated columns as follows.

Last visit date =
MAXX (
    FILTER (
        Table1,
        Table1[Name] = EARLIER ( Table1[Name] )
            && Table1[Order Date] < EARLIER ( Table1[Order Date] )
    ),
    Table1[Order Date]
)
Days since last visit =
DATEDIFF ( Table1[Last visit date], Table1[Order Date], DAY )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-chuncz-msft

 

Hi Sam,

 

Thank you for the response. I tried this, I am afraid, the formular is calculating wrong "Last visit dates". Any thoughts where the error could be coming from?

 

PS: Days since last visit calculation is also incorrect as it is connected to previous calculation.

 

Thanks,

John.  

 

 

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.