Problem Checking the Cost of Sales Value on a Monthly Basis: Shows Abnormal Values When Month Closin

[sameteskb]

Hello,
I have the following problem.
I have a value called cost of sales. I break this value into a table by month, and it averages around -400 million per month. However, if the month is not closed (for example, we are in September and the month is not over yet), this value shows very extreme numbers (like -4 billion). Only when the month closes does it bring me the correct value from the source.

When I select the whole year in the date slicer that I put at the top, I see very wrong values in the total result when it includes the months that are not closed and I want to apply a filter like this.

Let it check the cost of sales value for each month and if this value < -600.000 for the month it checks, that month should not appear in the date slicer. If I apply this, when I select all the months, I will ensure that the months that are not closed are not displayed.

Of course, it should also take the year into account when checking this process. In other words, when checking January, I think it should test January 2023 and January 2022 separately, not January 2022 + January 2023. My last sentence may be a little too detailed.

Thanks for your support in advance.

[123abc · Accepted solution]

It seems like you're dealing with a data visualization or reporting issue in a tool like Power BI, Tableau, or Excel, where you want to filter out months that are not yet closed to avoid displaying abnormal values. You're also looking to apply a filter that considers both the month and the year separately. Here's how you can approach this problem:

1. Create a Measure for Cost of Sales: In your data model, create a new measure that calculates the cost of sales for each month. This measure should be based on your source data and should not depend on the date slicer.

2. Create a Filter Measure: Create another measure that checks if the cost of sales for a specific month is less than -600,000. You can use an IF statement for this. Here's an example:

FilteredMonth = IF([Cost of Sales] < -600000, BLANK(), [Cost of Sales])

1. This measure will return the cost of sales if it's within the acceptable range, or BLANK() if it's not.

2. Apply the Filter in the Date Slicer: Now, you need to configure your date slicer to use the "FilteredMonth" measure instead of the raw date column. This will filter out months where the cost of sales is less than -600,000.

3. Year-Specific Filtering (Optional): If you want to apply the filter separately for each year, you can modify the "FilteredMonth" measure to consider the year as well. Here's an example of how you can do this:

FilteredMonth =
IF(
[Cost of Sales] < -600000 ||
YEAR([Date]) <> YEAR(TODAY()),
BLANK(),
[Cost of Sales]
)

1. This will filter out months where the cost of sales is less than -600,000 or where the year of the date is not the current year.

2. Apply the Year-Specific Filter in the Date Slicer (Optional): If you've implemented year-specific filtering, make sure to update your date slicer to use this modified "FilteredMonth" measure.

By following these steps, you should be able to filter out months with abnormal cost of sales values, and optionally, apply year-specific filtering. This should help you visualize your data more accurately in your reporting tool.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[sameteskb]

Thank you so much