cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dgharvey Frequent Visitor
Frequent Visitor

Summary of data over time

I am trying to summarize sales data over a 12 month period. All seems fine for 2018 data, but if I then look at 2017, the figures are around 2% lower than I was expecting. The DAX formula I am using is as follows:

 

Net Sales =
CALCULATE(SUMX(
    SUMMARIZE('Base Measures','Base Measures'[MARKET_CURRENCY], "Total Net Sales", SUM('Base Measures'[Net Sales])),
    [Total Net Sales] * [Measure 14])
    FILTER('Base Measure Modes', 'Base Measure Modes'[MEASURE_VARIANT_TYPE_CODE] = "LOCMKT"),
    EXCEPT(ALL('Legal Entities'),'Legal Entities'),
    FILTER(ALL('Date'), 'Date'[Date] in VALUES(DateFilter[Full Date]))
    )
 
The following may also help, which is the DAX for Measure 14:
Measure 14 =
var AOP_fx_rate = CALCULATE(VALUES('FX Rates'[EXCHANGE_RATE]), FILTER(ALL('FX Rates'),'FX Rates'[DIM_TO_CURRENCY_SK] = 192
     && 'FX Rates'[DIM_CURRENCY_EXCHANGE_TYPE_SK] = 1
     && 'FX Rates'[Full Date] = MAX('Months and Years'[Full Date])
     &&'FX Rates'[DIM_FROM_CURRENCY_SK] = MAX('Profitability Base Measures'[DIM_MARKET_CURRENCY_SK])))
return
AOP_fx_rate
2 REPLIES 2
Super User
Super User

Re: Summary of data over time

Any chance you can post sample data? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


dgharvey Frequent Visitor
Frequent Visitor

Re: Summary of data over time

Data as follows.

The correct figures for 2017 (Net Sales) should be:

OCT 158,417,666.30

NOV 165,816,723.30

DEC 148,189,184.90

data.PNG