Forum Discussion
Variations on December
Thank you for reaching out to Microsoft Fabric Community.
To solve the issue of customers disappearing in months where they had no sales (e.g., February), you should use a separate Customer dimension table and a proper Date table, both related to your fact table. This ensures you can maintain customer visibility even when they have no sales in a selected month. Use the Customer table in visuals (not the sales table) and enable "Show items with no data" in the visual settings. To calculate the variation from December of the previous year, regardless of the selected month or whether there were sales, use the following DAX measures:
1. Sales A in December of the Previous Year:
Sales A Dec =
CALCULATE(
SUM(Sales[Product A]),
KEEPFILTERS('Customer'[Customer]),
FILTER(
ALL('Date'),
FORMAT('Date'[Date], "YYYY-MM") = FORMAT(EDATE(MAX('Date'[Date]), -MONTH(MAX('Date'[Date]))), "YYYY-12")
)
)2. Sales Variation from December:
Sales A Variation =
VAR CurrentSales = CALCULATE(SUM(Sales[Product A]))
VAR DecSales = [Sales A Dec]
RETURN
IF(NOT ISBLANK(DecSales), CurrentSales - DecSales)
These measures ensure that even when a customer has no sales in a filtered month, they are still shown in the report with their December sales and corresponding variation.
If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.
Thank you very much for answering. Tell you that I can't share it because it contains confidential information.
I give you a table that has reduced data, in terms of customers, products... but the basic thing is that there is a monthly measurement (dd/mm/yyyy date column) per customer (column) and per product (column A - Column B)
You would need to obtain "balance variations" (not accumulations of sales...) if not, if in December you sold 100, in January 50, the variation when you filter in January appears as -50, if in February you sell 60, that the variation is -40, that if in March you do not sell, that -100 appears and so on. The problem appears when I filter by month, since the customers who did not sell the previous month disappear. I have created a parallel table where all the historical clients are and in the presentation I take this table, as well as a table of dates,
The table would be:Clientes.xlsx
You tell me how I do it. Copilot has given me over 15 measurements that don't work, it always disappears when I filter by February, the client that their last sale was in Dec-24
Thank you very much in advance.