Tableau Function to Power BI

[angelina_21]

I'm needing to migrate this Tableau code to Power BI and have had no luck doing so.

IF [Frequency]='Monthly' THEN
{ FIXED [Work Item ID],[Savings],[Frequency] : AVG(DATEDIFF('month',[Closed Date],TODAY())*[Amount]) }
ELSE
0
END

[v-tejrama]

Hi @angelina_21,

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]
)

Please find attached .PBIX file for your reference.

Best Regards,
Tejaswi.

[v-tejrama]

Hi @angelina_21 ,

I hope the information provided has been useful. Please let me know if you need further clarification

Thank you.

[v-tejrama]

Hi  @angelina_21,

Just wanted to check if you had the opportunity to review the suggestion provided?

Thank you.

[v-tejrama]

Hi @angelina_21 ,

I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

Thank you.

[grazitti_sapna]

Hi @angelina_21 

Please try the below measure:

MonthlyAmountCalc =
IF (
SELECTEDVALUE('Table'[Frequency]) = "Monthly",
AVERAGEX (
SUMMARIZE (
'Table',
'Table'[Work Item ID],
'Table'[Savings],
'Table'[Frequency],
"CalculatedValue", DATEDIFF('Table'[Closed Date], TODAY(), MONTH) * 'Table'[Amount]
),
[CalculatedValue]
),
0
)


 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[Ashish_Mathur]

Hi,

Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[danextian]

Hi @angelina_21 

It's been a long long while since I last used Tableu. Try below as a measure

Monthly Savings Estimate =
IF (
    SELECTEDVALUE ( 'DataTable'[Frequency] ) = "Monthly",
    AVERAGEX (
        SUMMARIZE (
            'DataTable',
            'DataTable'[Work Item ID],
            'DataTable'[Savings],
            'DataTable'[Frequency],
            "MonthsSinceClosed", DATEDIFF ( 'DataTable'[Closed Date], TODAY(), MONTH ) * 'DataTable'[Amount]
        ),
        [MonthsSinceClosed]
    ),
    0
)

[Daniel29195]

hello @angelina_21 ,
could you please share the business logic behind your measure ?

[Jai-Rathinavel · Accepted solution]

Hi @angelina_21 try the below measure and let me know if it helps.

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