Tableau Function to Power BI

Hi Anonymous,

To calculate the average of (months since Closed Date) * Amount specifically for rows where the Frequency is "Monthly" and the Closed Date is not blank, I created a DAX measure that handles this in a structured way. First, it filters the table to only include rows where Frequency is "Monthly" and Closed Date is present.

Then, using VALUES along with ADDCOLUMNS, it iterates over each unique combination of Work Item ID, Savings, and Frequency. For each combination, it calculates the average of DATEDIFF(Closed Date, Today, in months) * Amount.

Finally, it returns the average of these calculated values using AVERAGEX. This helps ensure the result respects both grouping and time-based logic accurately. You can add this measure to a matrix visual, with Frequency and Work Item ID in the rows, and it will return the correct average weighted savings for each item.

Here's the DAX used:

Monthly Estimated Savings =
VAR FilteredTable =
FILTER (
ALL ( 'Table' ),
'Table'[Frequency] = "Monthly" &&
NOT ISBLANK ( 'Table'[Closed Date] )
)
RETURN
AVERAGEX (
ADDCOLUMNS (
VALUES ( 'Table'[Work Item ID] & 'Table'[Savings] & 'Table'[Frequency] ),
"CalcVal",
VAR ItemID = [Work Item ID]
VAR SavingsVal = [Savings]
VAR Freq = [Frequency]
RETURN
AVERAGEX (
FILTER (
FilteredTable,
'Table'[Work Item ID] = ItemID &&
'Table'[Savings] = SavingsVal &&
'Table'[Frequency] = Freq
),
DATEDIFF ( 'Table'[Closed Date], TODAY(), MONTH ) * 'Table'[Amount]
)
),
[CalcVal]
)