As requirements for the analysis, I have the following:
- Perform a Like For Like analysis for the stores in the dataset;
- Consider only the two most recent years (dynamically);
- Interact with the data only with a month name slicer;
- Take into account the store's closing date: a. Values should not appear in dates of homologous periods later than the store's closing date (for example, if the store closed in 202405, when selecting the month of November, values for 202311 should not appear even if there were sales). b. But the accumulated values (YTD) should appear at most up to the closing date and the respective homologous period.
The model represents a small sample of the original dataset, which contains about 30 million rows. To simplify, I included only a DIM_Calendar table and a FAT_Sales table. The calendar table is self-explanatory and relates to the sales table by the "YYYYMM" and "TRANSACTION_DATE" columns, respectively, resulting in a many-to-many relationship. On the sales side, I currently do not have daily granularity, only monthly.
The FAT_Sales table contains sales from 2 consolidated stores, Store A and Store B. Store A has only 1 store (A1), and Store B has 2 stores (B1 and B2). This table also contains the quantities sold over time (since 2022), as well as the date from which Like For Like started, and the closing date if the store has closed (this field is null if the store is still active).
My approach started by creating a calculated column to evaluate in each row of FAT_Sales whether it was "LFL" or "Not LFL". After that, using DAX measures, I performed the calculations included in the blank measures of this report when it was "LFL". I am not getting the logic right and I am a bit desperate with this report. Any help is welcome!
Link to the report:
https://drive.google.com/file/d/1KsGhmOEsD3lF5HhjNXH29UUpxW-dMU9h/view?usp=sharing
