Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ID | Customer Number | Visit Date | Product Key | SKU Available |
1 | 1049699 | 19.03.2019 | 10096 | 1 |
1 | 1049699 | 19.03.2019 | 10884 | 0 |
1 | 1049699 | 19.03.2019 | 13065 | 0 |
2 | 1049699 | 02.04.2019 | 10096 | 0 |
2 | 1049699 | 02.04.2019 | 10884 | 1 |
2 | 1049699 | 02.04.2019 | 13065 | 1 |
3 | 1049699 | 02.05.2019 | 10096 | 1 |
3 | 1049699 | 02.05.2019 | 10884 | 0 |
3 | 1049699 | 02.05.2019 | 13065 | 1 |
4 | 1001268 | 01.04.2019 | 10096 | 0 |
4 | 1001268 | 01.04.2019 | 10021 | 1 |
4 | 1001268 | 01.04.2019 | 10037 | 0 |
5 | 1001268 | 01.03.2019 | 10014 | 0 |
5 | 1001268 | 01.03.2019 | 10021 | 1 |
5 | 1001268 | 01.03.2019 | 10037 | 0 |
6 | 1001268 | 06.05.2019 | 10014 | 0 |
6 | 1001268 | 06.05.2019 | 10021 | 1 |
6 | 1001268 | 06.05.2019 | 10037 | 0 |
7 | 1001268 | 06.06.2019 | 10014 | 0 |
7 | 1001268 | 06.06.2019 | 10021 | 1 |
7 | 1001268 | 06.06.2019 | 10037 | 0 |
Solved! Go to Solution.
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] ) )
Regards,
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] ) )
Regards,
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |