Measure - filter separate table, get value, multiply by value in column

[heiavieh]

My request doesn't feel difficult, but I can't get a handle on DAX and it's becoming frustrating. I have no clue how filtering arguments work with this language.

There are two tables. A workdays table, which appears this way:

Date	WorkdayNumber	MaxWorkDay	Month
1/1/2024	0	0	January
1/31/2024	22	1	January
2/29/2024	21	1	February

Basically, the table contains all of the days in a year. The WorkdayNumber field refers to the workday that day is in the month, notwithstanding holidays and weekends (holidays and weekends get zeroes).

If it's the last workday of the month, it gets a 1 in the MaxWorkDay field, otherwise its a 0.

My second table is an Actuals Accumulated table.

BusinessUnit	ActualAccumulated	Month
BU1	###	January
BU1	###	February
BU2	###	January

The only important columns here are the actual accumulated and the month. The calculation needed is projection, which is the amount of workdays in a month multiplied by the actual accumulated for that month.

Here's my desired outcome:

BusinessUnit	ActualAccumulated	Month	Projection
BU1	###	January	### * 22
BU2	###	January	### * 22
BU1	###	February	### * 21
BU2	###	February	### * 21

To make this occur, I thought to use the maximum workday of the month from the Workdays table. But I absolutely can't comprehend it.

Here's how it looks in my head: (All of my Power Platform experience is mostly Power Fx, so this will be strange looking)

Projection =

VAR maxDays = 
Filter(workdays_Table, 
   ActualAccumulatedTable[Month] = [Month] && MaxWorkDay = 1).[WorkdayNum]

I want to get the maxmimum workday amount from the Workdays table. So for whatever row I'm solving for, I want it to look for that month in the Workdays table, and also check to see if its the maximum day.

Once it gets that value, I want it to multiply the maximum day amount by whatever the Actual Accumulated is within the table.

var projectionProduct = maxDays * [ActualAccumulated]

RETURN
projectionProduct

I thought that a measure would be best for this, but I'm unsure if a column is a better decision. Any guidance is greatly appreciated.

[v-xuxinyi-msft · Accepted solution]

Hi @heiavieh 

Your solution is great, @DataInsights . Here I have another idea in mind, and I would like to share it for reference.

I add all the dates for January and February to the workdays table.

Then I created a measure as follows.

Projection = 
VAR _workdays = CALCULATE(MAX(workdays[WorkdayNumber]), FILTER(workdays, [Month] = SELECTEDVALUE('Actuals Accumulated'[Month])))
RETURN
MAX([ActualAccumulated]) * _workdays

Result:

Best Regards,
Yulia Xu

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

[DataInsights]

@heiavieh,

Try these measures. You could also create a calculated column (DAX) or custom column (Power Query). When a calculation isn't dependent on user selections (e.g., slicer), I prefer to create a column in Power Query or DAX to improve visual performance. Each model has its own requirements, though.

Sum ActualAccumulated = SUM ( ActualAccumulatedTable[ActualAccumulated] )

Projection = 
VAR vTable =
    ADDCOLUMNS (
        ActualAccumulatedTable,
        "@Projection",
            VAR vMonth = ActualAccumulatedTable[Month]
            VAR vWorkdayNumber =
                MAXX (
                    FILTER (
                        workdays_Table,
                        workdays_Table[Month] = vMonth
                            && workdays_Table[MaxWorkDay] = 1
                    ),
                    workdays_Table[WorkdayNumber]
                )
            RETURN
                [Sum ActualAccumulated] * vWorkdayNumber
    )
VAR vResult =
    SUMX ( vTable, [@Projection] )
RETURN
    vResult