Create a Running Total Measure in DAX from another Measure (not Calculated Column)

Question

First of all, though it might be somehow irrelevant, this is my first post in the community, which is due to the fact that I've only started to learn DAX last week. I consider myself fairly advanced in Excel, however I'm just starting to understand the capabilities of DAX and I'm here to learn.

I'll start with an explanation of what I'm trying to do.

I have 2 different tables, one DimensionsExchangeRate table and one FactsSales table.

The FactsSales table contains sales from 2 countries, USA (in USD) and UK (in GBP), while the DimensionsExchangeRate table contains by-monthly Dates and Exchange Rates (USD to GBP). What I want is to convert all the sales in GBP and also to have Running Total Measure for Sales GBP.

I have managed to accomplish both things, and I did it by creating two Calculated Columns in the FactsSales table (Exchange Rate column and Product Sales GBP column). After this, I managed to create the Running Total Product Sales GBP column.

Now you may be thinking "Alright, good for you, now stop bragging and tell us what you want". I want to create Measures for everything, and not one single Calculated Column.

In fact, with some help from Marco Russo (God almighty), I have managed to write the Exchange Rate measure, and I have also managed to write the Product Sales GBP measure; so basically you'd think that I don't need any of those columns. However, what I'm having trouble with is creating the Running Total Product Sales GBP measure unless my Product Sales GBP is a Calculated Column. If it is a measure, I can't manage to get it to work.

I will write below the formulas I've used, so you can understand better. First, this is how my DimensionsExchangeRate Table looks like.

DATE LOWER UPPER EXCHANGE RATE

12/15/15 11/18/15 1.523

11/18/15 10/25/15 11/18/15 1.512

10/25/15 9/8/15 10/25/15 1.554

9/8/15 9/8/15 1.593

Basically, the Exchange Rate is to be used for Sales between Lower and Upper.

1)

Exchange Rate Measure:=AVERAGEx(FactsSalesTable, 1 *
    LOOKUPVALUE(DimensionsExchangeRate[Exchange Rate],DimensionsExchangeRate[Date],
    CALCULATE(MAX(DimensionsExchangeRate[Date]),
    FILTER(DimensionsExchangeRate, FactsSalesTable[Date]>=DimensionsExchangeRate[Lower])))) -> this measure works ok.

2)

Exchange Rate Calculated Column = LOOKUPVALUE(DimensionsExchangeRate[Exchange Rate],DimensionsExchangeRate[Date],
    CALCULATE(MAX(DimensionsExchangeRate[Date]),
    FILTER(DimensionsExchangeRate, FactsSalesTable[Date]>=DimensionsExchangeRate[Lower]))) -> this calculated column works ok

3)

Product Sales GBP Measure:=SUMX(FactsSalesTable, IF(FactsSalesTable[Country]="USA", FactsSalesTable[Product Sales]/
    AVERAGEx(AA5, 1 *
    LOOKUPVALUE(DimensionsExchangeRate[Exchange Rate],DimensionsExchangeRate[Date],
    CALCULATE(MAX(DimensionsExchangeRate[Date]),
    FILTER(DimensionsExchangeRate, FactsSalesTable[Date]>=DimensionsExchangeRate[Lower])))),
    FactsSalesTable[Product Sales])) -> this measure works ok

However now, when I try to create the Product Sales To Date GBP Measure (aka "Running Total"), I can't quite get it to work.

4)

Product Sales To Date GBP:=Calculate(SUMX(FactsSalesTable, IF(FactsSalesTable[Marketplace]="USA", FactsSalesTable[Product Sales]/

    AVERAGEx(FactsSalesTable, 1 *
    LOOKUPVALUE(DimensionsExchangeRate[Exchange Rate],DimensionsExchangeRate[Date],
    CALCULATE(MAX(DimensionsExchangeRate[Date]),
    FILTER(DimensionsExchangeRate, FactsSalesTable[Date]>=DimensionsExchangeRate[Lower])))),

    FactsSalesTable[Product Sales])),
    FILTER(ALLEXCEPT(FactsSalesTable,DimensionsProducts[Product],DimensionsProducts[Country]), FactsSalesTable[Date] <=MAX (FactsSalesTable[Date] ) ))

The problem with this formula is that the “Calculate(Max())” function (from the “AverageX”) will return the first date that has an ExchangeRate from the DimensionExchangeRate table, and for the first dates from the FactsSalesTable (which are before the date that “Calculate(MAX)” retrieves ) I get a NUM! error, because of the outer “Filter” (of the first “Calculate” function).

For example, if my first (oldest) date in the DimensionsExchangeRate table is Apr/1/2015, and my FactsSalesTable contains dates starting from Jan/1/2015, I’d get errors for dates: Jan/1/2015 – Mar/31/2015.

As I've said, in order to get the job done, I had to insert a Calculated Column for the Product Sales GBP =IF(FactsSalesTable[MarketPlace]="USA",FactsSalesTable[Product Sales]/FactsSalesTable[Exchange Rate],FactsSalesTable[Product Sales])

And now, because I have a Calculated Column, I can easily create a measure for the Running Total.

Total Product Sales GBP To Date:=CALCULATE (SUM (FactsSalesTable[Product Sales GBP] ),
    FILTER(ALLEXCEPT(FactsSalesTable,DimensionsProducts[Product],DimensionsProducts[Country]),  FactsSalesTable[Date] <= MAX ( FactsSalesTable[Date] )))

Again, what I want is to make the Measure for the Running Total Product Sales GBP to work, without having the Calculated Column for the Product Sales GBP. I'm sorry for the long post, but it was so I can explain clearly the situation.

greggyb greggyb · Accepted Answer · 2016-01-09T03:02:46

See response on TechNet forums.

GENERATE() is used to create a table that includes the appropriate exchange rate on each row of the fact table. Then the SUMX() becomes very simple. This all happens in whatever filter context is being applied to the fact table, either directly or through dimensions related to it.

SUMX() becomes quite simple then. Since the whole thing behaves well with filter context, standard time intelligence functions can be used, simply filtering on the date table.

SalesGBP:=
SUMX(
    GENERATE(
        FactSale
        ,FILTER(
            ExchangeRate
            ,ExchangeRate[LOWER] <= EARLIER( FactSale[Date] )
                && ( ExchangeRate[UPPER] > EARLIER( FactSale[Date] )
                        || ISBLANK( ExchangeRate[UPPER] )
                    )
        )
    )
    ,IF(
        FactSale[Country] = "USA"
        ,FactSale[Sales Amount] / ExchangeRate[EXCHANGE RATE]
        ,FactSale[Sales Amount]
    )
)

Sales:=
IF(
    HASONEVALUE( Display[DisplayCurrency] )
    ,IF(
        VALUES( Display[DisplayCurrency] ) = "USD"
        ,[SalesUSD]
        ,[SalesGBP]
    )
)

SalesYTD:=
TOTALYTD( [Sales], DimDate[Date] )

Create a Running Total Measure in DAX from another Measure (not Calculated Column)

1 Answers