DAX - Unable to produce SUMX cumulative result measure (probably due to table as a variable?)

[Anonymous]

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

[PaulDBrown · Accepted solution]

Try:

Cumulative =
CALCULATE (
    [AggregatedSales measure],
    FILTER ( ALL ( Dim_Date ), Dim_Date[Date] <= MAX ( Dim_Date[Date] ) )
)

[Anonymous]

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! 

[amitchandak]

@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))

[Anonymous]

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

[Anonymous]

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.