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.
I have an issue i just cant crack, to be honest its probably due to a poor model or a bad understanding of how DAX actually works.
I have a model with a FACT_Data table on a daily level with 1 currency
a DIM date table on daily level [DIM_DATE]<[FACT_Data]
A DIM currency table with manyu CURRENCIES on Daily level [DIM_DATE]<[DIM_Currency]
the users select a currency and the final measure changes. multiplying the exchange rate by the value monthly
I SUMMARIZE the data table to monthly
the use the following dax
VAR AggregatedSales =
ADDCOLUMNS (
SUMMARIZE (
Fact_Data,
DIM_Date[Date], -- Month granularity,
DIM_Date[Financial Year],
DIM_Date[FinancialMonthNumber]
),
"@Rate", SELECTEDVALUE ( DIM_Currency[ExchangeRateRate] ),
"@Amount", CALCULATE(SUM(Fact_Date[Value]))
)
VAR Result =
SUMX (
AggregatedSales,
[@Amount] * [@Rate]
)
RETURN
Result
i do not know how to make this cumlative.
ive done cumulative stuff before using daxpatterns website but feel i must be doing something wrong here fundementally, it shouldne be this hard
Solved! Go to Solution.
Try:
Cumulative =
CALCULATE (
[AggregatedSales measure],
FILTER ( ALL ( Dim_Date ), Dim_Date[Date] <= MAX ( Dim_Date[Date] ) )
)
Proud to be a Super User!
Paul on Linkedin.
Try:
Cumulative =
CALCULATE (
[AggregatedSales measure],
FILTER ( ALL ( Dim_Date ), Dim_Date[Date] <= MAX ( Dim_Date[Date] ) )
)
Proud to be a Super User!
Paul on Linkedin.
thank you! so all i had to do was take the last bit out of the measure?
i was trying this inside the same measure and it just wouldnt work.
Question..why does this have to be in a separate measure.
thanks so much, i feel stupid not thinking of this!
@Anonymous , Try measure like
measure =
var _rate =SELECTEDVALUE ( DIM_Currency[ExchangeRateRate] )
return
SUMX (filter(allselected('DIM_Date') , DIM_Date[Date] <= max(DIM_Date[Date])), CALCULATE(SUM(Fact_Date[Value])*_rate))
also, the first month is correct, but not the second. i think (im not sure) some how the currency conversion is not getting mapped correctly.
goinng to check the data model now and see if theres an issue
this doesnt seem to work i dont know why, my non cumulative figures (added up) and cumulative figures do not match when a currency is applied
works fine for native (unconverted) currency.