New column not giving correct results when using date to filter by month

[Belle2015]

Example power BI 

Excel Data 

I have had help to create the below DAX function - 

However I have run into a problem when I went to use it in my actual data where I want to apply a date filter to see the values by each month, I hadnt included the dates in my sample data, would I need to write something into the calculation to have it calculating by the date as at the minute it gets the total of scrap by process correct for the year to date but when you split it out by the months the values by the date are not correct. 

Allocated Scrap =

VAR CurrentProcess = 'Work Order Value'[Process]

VAR MachineWork = 'Work Order Value'[Work Value]

VAR TotalProcessWork =

    CALCULATE(

        SUM('Work Order Value'[Work Value]),

        ALLEXCEPT('Work Order Value', 'Work Order Value'[Process])

    )

VAR MachineCount =

CALCULATE(

    DISTINCTCOUNT('Work Order Value'[Machine]),

    ALLEXCEPT('Work Order Value', 'Work Order Value'[Process])

)

VAR TotalScrap = CALCULATE(

        SUM('Scrap'[Value Scrapped]),

        ALLEXCEPT('Scrap', 'Scrap'[Process])

    )

RETURN

IF(

    TotalProcessWork > 0,

    TotalScrap * (MachineWork / TotalProcessWork),

    TotalScrap / MachineCount

)

Many thanks in advance for your help. 

[Stiffi88]

Hi @Belle2015 for me this Link isn't working.

[Belle2015]

Sample Power BI 

Hi @Stiffi88 , 

Thanks for getting back to me, hopefully this link will work 🤞

[Stiffi88]

Hi Belle2015,

I guess, you have to expant you ALLEXEPT with the Date. But here ist a very good article about this topic.

Using ALLEXCEPT versus ALL and VALUES - SQLBI

KR
Clemens

[Elena_Kalina]

Hi @Belle2015 

You need to try modifying your DAX formula to respect the date context. 

Allocated Scrap =
VAR CurrentProcess = 'Work Order Value'[Process]
VAR MachineWork = 'Work Order Value'[Work Value]
VAR TotalProcessWork =
    CALCULATE(
        SUM('Work Order Value'[Work Value]),
        ALLEXCEPT('Work Order Value', 'Work Order Value'[Process], 'Date'[Date]) // Include date table in context
    )
VAR MachineCount =
    CALCULATE(
        DISTINCTCOUNT('Work Order Value'[Machine]),
        ALLEXCEPT('Work Order Value', 'Work Order Value'[Process], 'Date'[Date]) // Include date table in context
    )
VAR TotalScrap = 
    CALCULATE(
        SUM('Scrap'[Value Scrapped]),
        ALLEXCEPT('Scrap', 'Scrap'[Process], 'Date'[Date]) // Include date table in context
    )

RETURN
IF(
    TotalProcessWork > ,
    TotalScrap * (MachineWork / TotalProcessWork),
    TotalScrap / MachineCount
)

If you don't have a date table, you could use the month columns directly (though a date table is recommended)

Allocated Scrap =
VAR CurrentProcess = 'Work Order Value'[Process]
VAR CurrentMonth = SELECTEDVALUE('Work Order Value'[Month]) // Or your month column
VAR MachineWork = 'Work Order Value'[Work Value]
VAR TotalProcessWork =
    CALCULATE(
        SUM('Work Order Value'[Work Value]),
        ALLEXCEPT('Work Order Value', 'Work Order Value'[Process], 'Work Order Value'[Month]),
        'Work Order Value'[Month] = CurrentMonth
    )
// Similar modifications for other variables

[Belle2015]

Hi @Elena_Kalina, 

Thanks for the help. 

I have tried the above with my calendar table but it then doesnt give any value for the scrap in my table? 

[bnjmnnl]

It may have something to do with the ALLEXCEPT function in your measure. Try adding your date within this function, as this function removes other filters.
Please let me know if this works?

[Belle2015]

Hi @bnjmnnl 

When I added this in the table now comes up blank for the scrap!

[bnjmnnl]

Did you make sure you have the right date columns from your scrap and work order value tables? Or a relation between?

[Belle2015]

HI @bnjmnnl , 

I have a relationship between the calendar and the scrap and the calendar and the work order value and I used the calendar in the DAX formula

[bnjmnnl]

Hi, Maybe it has something to do with the ALLEXCEPT function, as this removes all other filters. Try adding date here.

Maybe this will work?