Looking to exclude items with no total

[benwann1]

I perform an analysis where we look at the materials that we produced in the current month and compare the usage of components to the same period last year.

The difference calculation works when a product was produced both this and last year. However, I need to exclude materials from the calculation that were only produced this year but not last year or last year but not this year.

I’ve spent a day and a half and I’m not close to an answer. It seems that if I build the logic into the iterating calculations, it applies it to the component/material level instead of the total material level.

How do I exclude the materials for the specified condition?

Do I need to modify the code or add new code elsewhere?

Component Difference =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Unique Mat Comp Plt',
            'Unique Mat Comp Plt'[Material],
            'Unique Mat Comp Plt'[Component Material]
        ),
        IF (
            [Component LBs CY] - [CY LBS at PY Rate] = 0,
            - [Component LBS PY],
            [Component LBs CY] - [CY LBS at PY Rate]
        )
    )
)
![No%20PY%20volume|689x222](upload://qgLFJ3GxMqo0DFINYCs2WqwCSuP.png)

[GarryA]

@benwann1

I perform an analysis where we look at the materials that we produced in the current month

When I want figures for the “current month”, “Current Year”, “Current Quarter” etc from a fact table I normally use a month offset in the Date table and then write measures filtered by the columns in the Date table.

For example, below is a measure for Customer Sales Last Month. So an offset of -1 shown below which represents Last Month and 0 = current month.

Customer Sale Invoices This Month =
IF (NOT
( ISBLANK ( [Customer Sale Invoices]) ),
CALCULATE
([Customer Sale Invoices],
‘DimCalendar’[CurrentMonthOffset] = 0 ))

Customer Sale Invoices Last Month =
IF (NOT
( ISBLANK ( [Customer Sale Invoices]) ),
CALCULATE
([Customer Sale Invoices],
‘DimCalendar’[CurrentMonthOffset] = -1 ))

Then you can work out the difference between each measure for a variance.

Try and break down each measure and see if you can get the right results at a total level.

[benwann1]

Thanks @GarryA. Can you explain how you setup the CurrentMonthOffset in your date table?

[GarryA]

@benwann1 @sam.mckay @Melissa it’s been asked before here. Plus Sam has date tables in his resources and a bunch of videos on this.

Here are the answer’s from Sam and Melissa which are on the money. :)

Calendar for current month and future 11 months

Yes nice one Melissa, You can solve it in the date table quite easily with a new column. You could also do this with a calculated column as well. For question 2 (in future can we place different questions in different forum posts) This is quite easy as well. I think you just want to select both at the same time. I don’t think you really need to create anything new. The user should just be able to see that they can select multiple items in the slicer. This way they can have the flexibility to …

So just further to this, the current month needs the current date worked out in your date table logic first.

CurrentDate = DateTime.Date(DateTime.FixedLocalNow()),

Then you can add the current month offset m-code to your calendar query like this from the advanced editor window.

CurrentMonthOffset = Table.AddColumn(PreviousStepName, “CurrentMonthOffset”, each (Date.Year([Date])- Date.Year(CurrentDate) ) * 12 + Date.Month([Date]) - Date.Month(CurrentDate), Int64.Type),

This will extract the relevant month number of the current month.