The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
94 | |
86 | |
70 | |
65 |
User | Count |
---|---|
244 | |
127 | |
119 | |
81 | |
78 |