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
Magigian23
New Member

Relative most recent visit date per customer and most recent vist date prior month

Hi Togehter,

 

I m trying to build a report where i can see the available SKUs based on the most recent field sales visit date per customer and the most recent field visit prior month as I d like to make a comparison versus last month. 

Means if my actual reporting is June, then should the report take the last recent visit for the customer. In below table would be the max date for the customer 1049699 the datas of the vist ID 3 from 06.05.2019. The Report should considerate the visit dates  from month Jan to June, while for the comparison vs pre month he should just considerate the most recent visit date from Jan to May. 

 

I was trying to build up with the Dax Function maxdate, but that give me just the highest date value accross all rows and not take the customer number into account. I also tried the group function max and created a max date per customer, but when i link it back to my table stand the maxdate like my visit date. For the Prior month i was thinking to use the calendar month offset funktion.

 

I have listed below an example of my table and hope that someone can help me further with my issue

 

BR

 

Benjamin 

 

 

 

Visit IDCustomer NumberVisit DateProduct KeySKU Available
1104969919.03.2019100961
1104969919.03.2019108840
1104969919.03.2019130650
2104969902.04.2019100960
2104969902.04.2019108841
2104969902.04.2019130651
3104969902.05.2019100961
3104969902.05.2019108840
3104969902.05.2019130651
4100126801.04.2019100960
4100126801.04.2019100211
4100126801.04.2019100370
5100126801.03.2019100140
5100126801.03.2019100211
5100126801.03.2019100370
6100126806.05.2019100140
6100126806.05.2019100211
6100126806.05.2019100370
7100126806.06.2019100140
7100126806.06.2019100211
7100126806.06.2019100370

 

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

Hi @Magigian23 

You may use below measures to get recent date.

Recent date =
VAR recent_date =
    CALCULATE (
        MAX ( Table1[Visit Date] ),
        ALLEXCEPT ( Table1, Table1[Customer Number] )
    )
RETURN
    IF ( MAX ( Table1[Visit Date] ) = recent_date, MAX ( Table1[Visit Date] ) )
Pre recent date =
VAR recent_date =
    CALCULATE (
        MAX ( Table1[Visit Date] ),
        ALLEXCEPT ( Table1, Table1[Customer Number] )
    )
VAR pre_recent_date =
    CALCULATE (
        MAX ( Table1[Visit Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Customer Number] ),
            Table1[Visit Date] < recent_date
        )
    )
RETURN
    IF ( MAX ( Table1[Visit Date] ) = pre_recent_date, MAX ( Table1[Visit Date] ) )

a1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-cherch-msft
Employee
Employee

Hi @Magigian23 

You may use below measures to get recent date.

Recent date =
VAR recent_date =
    CALCULATE (
        MAX ( Table1[Visit Date] ),
        ALLEXCEPT ( Table1, Table1[Customer Number] )
    )
RETURN
    IF ( MAX ( Table1[Visit Date] ) = recent_date, MAX ( Table1[Visit Date] ) )
Pre recent date =
VAR recent_date =
    CALCULATE (
        MAX ( Table1[Visit Date] ),
        ALLEXCEPT ( Table1, Table1[Customer Number] )
    )
VAR pre_recent_date =
    CALCULATE (
        MAX ( Table1[Visit Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Customer Number] ),
            Table1[Visit Date] < recent_date
        )
    )
RETURN
    IF ( MAX ( Table1[Visit Date] ) = pre_recent_date, MAX ( Table1[Visit Date] ) )

a1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.