Calculating and returning the value for each date in the calendar

[TranquilBoy]

Hello everyone!

Hope you all are doing well!

I am stuck with quite an issue here. Hope you can give me a few hints how to go on.

So, I have a dataset from 1 fact table and 4 dimensions:

The fact table has the next structure:

The problem is I can't quite get my head around how to get the desired result.

I need to produce the output of the next spreadsheet formula:

=SUMIFS(N:N;L:L;">="&Q3;G:G;"AUD";$K:$K;"<="&MIN($Q$3:$Q$46);$J:$J;MIN($Q$3:$Q$46))/SUMIFS(M:M;L:L;">="&Q3;G:G;"AUD";K:K;"<="&MIN($Q$3:$Q$46);J:J;MIN($Q$3:$Q$46))

The logic behind calculation:

- aggregate all calculated amounts ("CalculatedAmount" column; difference between Value date and Valuation date columns multiplied by the Amount) where:

• Trade date is less than or equal to minimal date in user selected set;
• Value date is bigger or equal to the date from user selected set;
• Valuation date is equal to the min date is user selected set

- divide the formula above to the Amount aggregation with the same filter

I created the next DAX measure:

_calculation = 
VAR _minDate = MIN(DimDates[Date])
VAR _filteredTable = FILTER(FactTable,AND(AND(FactTable[Trade Date]<=_minDate,FactTable[Valuation Date] = _minDate),FactTable[Value Date]>=FactTable[Creation Date]))
VAR _result = SUMX(_filteredTable,(DATEDIFF(FactTable[Valuation Date],FactTable[Value Date],DAY)))
return _result

Power BI returns the correct value, which is 41:

However, I need to get result similar to SUMIFS one. The dataset satisfies the requirements not only for 29/10:

I have a hinch, that this is the result of inner join between dates and fact tables, but I do not see the solution of this issue.

How can I do this? Any hints or help will be appreciated!

pbix and xlsx files can be downloaded here: https://drive.google.com/drive/folders/1Moh-Icg24sCvI7tGXvUCWajm87II-IVE?usp=sharing

Thank you and have a nice day!

[Icey · Accepted solution]

Hi @TranquilBoy ,

Please check if this is what you want:

CalculatedAmount Column =
DATEDIFF ( FactTable[Valuation Date], FactTable[Value Date], DAY ) * FactTable[Amount]

Measure 1 = 
VAR CurrentUserDate_ =
    MIN ( DimDates[Date] )
VAR MinxUserDate_ =
    MINX ( ALLSELECTED ( DimDates[Date] ), DimDates[Date] )
VAR SUM_CalculatedAmount =
    CALCULATE (
        SUM ( FactTable[CalculatedAmount Column] ),
        FactTable[Value Date] >= CurrentUserDate_,
        FactTable[Trade Date] <= MinxUserDate_,
        FactTable[Valuation Date] = MinxUserDate_,
        ALLSELECTED ( DimDates[Date] )
    )
VAR SUM_Amount =
    CALCULATE (
        SUM ( FactTable[Amount] ),
        FactTable[Value Date] >= CurrentUserDate_,
        FactTable[Trade Date] <= MinxUserDate_,
        FactTable[Valuation Date] = MinxUserDate_,
        ALLSELECTED ( DimDates[Date] )
    )
RETURN
    DIVIDE ( SUM_CalculatedAmount, SUM_Amount )

Best Regards,

Icey

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

[TranquilBoy]

Hello, Icey.

Works just fine! Thanks a lot. At last I can get the progress moving forward!

Wishing you a very great day!