Forum Discussion
context change
desired output
Month Total 2023 Total 2024 LFL 2023 LFL 2024
| Jan | 20 | 35 | 20 | 27 |
| Feb | 21 | 31 | 21 | 22 |
| Mar | 20 | 37 | 20 | 28 |
| Apr | 32 | 33 | 12 | 33 |
Goal:
- Total 2023: Sum the sales for 2023 for each month.
- Total 2024: Sum the sales for 2024 for each month.
- LFL 2023: For each month, calculate the total 2023 sales only for stores that have sales in both 2023 and 2024 for that same month.
- LFL 2024: For each month, calculate the total 2024 sales only for stores that have sales in both 2023 and 2024 for that same month.
data
Month Store 2023 Sales 2024 Sales
| Jan | S23 | 10 | 12 |
| Jan | S24 | 10 | 15 |
| Jan | S25 | (blank) | 8 |
| Feb | S23 | 12 | 11 |
| Feb | S24 | 9 | 11 |
| Feb | S25 | (blank) | 9 |
| Mar | S23 | (blank) | 9 |
| Mar | S24 | 15 | 18 |
| Mar | S25 | 5 | 10 |
| Apr | S23 | 20 | (blank) |
| Apr | S24 | 2 | 21 |
| Apr | S25 | 10 | 12 |
3 Replies
Create the following measures to calculate the totals and LFL (Like-for-Like) sales.
Total 2023 Sales = SUM('Sales'[2023 Sales])
Total 2024 Sales = SUM('Sales'[2024 Sales])
LFL 2023 Sales =
CALCULATE(
SUM('Sales'[2023 Sales]),
FILTER(
'Sales',
NOT(ISBLANK('Sales'[2023 Sales])) && NOT(ISBLANK('Sales'[2024 Sales]))
)
)
LFL 2024 Sales =
CALCULATE(
SUM('Sales'[2024 Sales]),
FILTER(
'Sales',
NOT(ISBLANK('Sales'[2023 Sales])) && NOT(ISBLANK('Sales'[2024 Sales]))
)
)Go to the "Visualizations" pane and select the "Table" visualization.
Drag the "Month" column to the table.
Drag the measures you created (Total 2023 Sales, Total 2024 Sales, LFL 2023 Sales, LFL 2024 Sales) to the table.- this has to be done at store level without keeping store at that views and check each store via months only when sales is present in both the month that store should be calculated in total sales for that month
LFL 2023: For each month, calculate the total 2023 sales only for stores that have sales in both 2023 and 2024 for that same month. - LFL 2024: For each month, calculate the total 2024 sales only for stores that have sales in both 2023 and 2024 for that same month. please note
- this has to be done at store level without keeping store at that views and check each store via months only when sales is present in both the month that store should be calculated in total sales for that month
Hi,
PBI file attached.
Hope this helps.
