Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculation with non-visulated data

Hello together   Here is my initial situation:   Value Date Payment PC IRR Purchased Effective Duration 24-Okt-2022 -7'457'843.06 2.019226 6.97 25-Okt-2022 -14'827'458.33 1.36793...
calculation
Date
DAX
Dynamic
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

The general mechanism is to create a summary table in a variable, which has the results of the measure at the day level, and then run an aggregation like SUMX or AVERAGEX over summary table. As an example, your weighted yield could be

Weighted Yield Monthly =
VAR SummaryTable =
    ADDCOLUMNS ( VALUES ( 'Date'[Date] ), "@value", [Weighted Yield] )
VAR Result =
    SUMX ( SummaryTable, [@value] )
RETURN
    Result

Although I put monthly in the name of the measure, it would actually work at any granularity of date.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Sorry, but I have not been working with Power BI for long and I think the formulas I have written are wrong.

I have already spent several hours with it and even with your approach I can't find a solution.