Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
- 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!
Solved! Go to 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.
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.
Hello, Icey.
Works just fine! Thanks a lot. At last I can get the progress moving forward!
Wishing you a very great day!