Variations on December

[Syndicate_Admin]

Hi, I'm new to the Forum and I've been using Power BI for a couple of weeks now.

I have a problem that I have already dedicated a few hours and many consultations to Copilot and I cannot solve.

Let me tell you:

Customers with sales of several products per month.

The customer is in one column (customer), the sales amount in euros of each product is in another (one column per product) (column Product A, column Product B). The sales amount is assigned to the last day of each month (dd/mm/yyyy) which is another colunma (date).

I want to obtain the sales variation per customer of product A over December of the previous year.

I have a measure that is "Sales A Dec", the variation works while the customer has sales in the previous month.

Specifically, I have an XXX customer to whom I sold product A for 1,000 eur in Dec-24 and nothing in January or February

In the reports when I filter January, if the sales to XXX of product A appear in Dec and that I have not sold anything in January, and therefore I appear both amounts

The problem comes when it leaked in February and the customer disappears both from sales to December and from variation to December.

I have thought that the problem comes from the autofilter that when you combine customer and month, if it does not find the customer in that month, it disables their vision, but I want it to appear because it is important to see the customers that have been lost throughout the year

To do this, I have created a table on the outside that has all the clients and I have linked it

It still doesn't work, I don't really know how to do it.

I would appreciate it if someone could give me a hand.

Thank you

[v-priyankata]

Hi @Syndicate_Admin 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @Syndicate_Admin 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-sgandrathi]

Hi @Syndicate_Admin,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you.

[v-priyankata · Accepted solution]

Hi @Syndicate_Admin 

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.

[Syndicate_Admin]

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.

[Sergii24]

@Syndicate_Admin, please share the sample pbix file, so we can try to help you 🙂

[Syndicate_Admin]

Above you have the link to the table and the comments. Slds and thank you very much in advance.