Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
Brief : There are multiple visits to a particular store in week/month for different kind of item checks by a person.
Visit Date | Item | Store | Item Present/Absent | Week |
1-Jan-21 | A | ABC | Absent | 1 |
2-Jan-21 | B | CDF | Present | 1 |
3-Jan-21 | B | XYZ | Present | 1 |
4-Jan-21 | A | FMS | Present | 1 |
29-Jan-21 | A | ABC | Present | 4 |
31-Jan-21 | B | CDF | Absent | 4 |
1-Feb-21 | B | PLO | Absent | 5 |
1-Feb-21 | B | CDF | Present | 5 |
2-Feb-21 | A | ABC | Present | 5 |
3-Feb-21 | A | GHY | Present | 5 |
Above is the scenario. Consider week as calendar weeks (data here might be different , for logic puspose I have considered 1 Jan 2021 as week 1).
CASE 1 :
User Selects Jan in time slicer.
Expected Output is
Item | Present in Stores | Absent in Stores | Total Unique Stores for Item |
A | 1 | 1 | |
B | 1 | 1 | 2 |
This needs to happen dynamically as in , when multiple months are selected only the latest instance of unique visit(Item+Date+Store) should be considered for absent or present calculation.
Latest visit may not necessarily fall on last date of selected period.
CASE 2 :
User selects Jan and Feb in slicer
Expected Output
Item | Present in Stores | Absent in Stores | Total Unique Stores for Item |
A | 3 | 1 | |
B | 2 | 1 | 3 |
Kindly help.
Hi, @Anonymous
How to get the desired result? Could you please consider sharing more details about it?
It makes it easier to give you a solution.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous The LASTDATE() function should help you here. Note it will use the filters you have selected so will show the last date that the product/store has a row in the table.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
LastDate() did not seem to fulfil the requirement. But Calculating a virtual table with MAXDATE function seems to work. Thanks for your time . Appriciate it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |