SUM of measure which uses LASTNONBLANK

[Anonymous]

Hi guys,

I have a problem with a DAX formula that I hope you can help me with;

I have 3 measures;

• Employees:=CALCULATE(SUM([Employees]);LASTNONBLANK('Date'[Date];CALCULATE(SUM([Employees_row]))))
  
  The number of employees the company has in a given month. Because the number of employees can not be summarized we use LASTNONBLANK in the measure formula 
  
  
• Workhours per month:=SUM([Workhours])
  
  
• Available workhours:=[Employees] * [Workhours per month]

Now as you can see in the screenshot above the calculations work fine for each of the individual months, but since the formula of the last measure is [Employees] * [Workhours per month] and not a sum of each of the months the total is wrong.

How could I solve this?

[Stachu]

you need SUMX to do multiplication first and then sum, and for right granularity you need to summarize first

e.g. something like this

Available workhours =
VAR __SummaryPerMonth =
    ADDCOLUMNS (
        SUMMARIZE ( Date, Date[Year]. Date[Month] ),
        "Emp", [Employees],
        "WH", [Workhours per month]
    )
RETURN
    SUMX ( __SummaryPerMonth, [Emp] * [WH] )

[Anonymous · Accepted solution]

I should probably have mentioned this (sorry) but I'm doind this in an SSAS Tabular model...does this approach still work? I tried to add it but can't get it to calculate properly (never used variables before).

BUT; with the help of a colleage and quite a few iterations (and with your concept of using the date-dimension instead of the fact table for the SUMMARIZE we managed to pull this together that worked:

Available workhours:=
	SUMX(
		SUMMARIZE('Date';
			Date[Year];
			Date[MonthNumber];
			"WH";
			MAX([Employees_row]) *
			CALCULATE(
				SUM([Workhours_row] ); 
				ALL('Projects') 
				)
			);
			[WH])

[Stachu]

depends on which version of SSAS you're using

https://www.sqlbi.com/articles/variables-in-dax/

I think it's only available in SSAS 2016 and onwards, for sure in Azure AS, PowerBI and Excel 2016