Force single value from slicer, disregarding applied grouping on slicer

[fcastle]

I’ve been fiddling around with this the whole day yesterday, and I seem to be unable to find a solution on my own.

I’m trying to create a traditional balance sheet report in PowerBi where the user selects one or more legal entities and a cut-off date (slicer), there are multiple currencies involved, and I need to recalculate the values in ie  from 01.01.2017 to 31.03.2017 using the exchange rate on the 31.03.2017 for all transactions in the periods, This is where I get into trouble.

If I do this:

mMaxDate = date(2017;3;31)

my calculations works as intended, but by doing this I’m circumventing the slicer, I want the mMaxDate measure to simply return the latest date in my slicer while simultaneously ignoring the grouping per month, I need a scalar value and that is it.

Thus this:

mDate = values(YearMonth[EndofMonth])
mMaxDate = date(year(DATEVALUE([mDate]));month(DATEVALUE([mDate]));DAY(DATEVALUE([mDate])))

nor

mMaxDate = calculate(max(YearMonth[EndofMonth]))

or

mMaxDate = calculate(max(YearMonth[EndofMonth]);filter(ALLSELECTED(YearMonth[EndofMonth]);YearMonth[EndofMonth] = max(YearMonth[EndofMonth])))

does not work.

I understand that my attempts are really variations of the same thing, as they all return a maxdate using the period grouping, which means that my measures returns  31.01.2017,28.02.2017 and 31.03.2017, and not just the 31.03.2017 I’m looking for.

Any insights would be greatly appreciated!

BR

fcastle

[spuder]

Hi @fcastle

I'm not sure if I got the problem. I connected the dates in the fact table with a datetable and used this for the calculation.

Sorry. I should read twice. My first measure was wrong so I updated it.

maxslicer = CALCULATE(LASTDATE(DateTable[Datekey]);ALLSELECTED())

[fcastle]

Hi spruder,  firstly; thank you for taking the time to have a look at my issue.

It might be someting with the way my other measures interact with the Slicer, I get a different issue with your

mMaxDate = CALCULATE(LASTDATE(DateTable[Datekey]);ALLSELECTED())

it returns a date, but my calculations go blank?

Trying this:

mMaxDate = calculate(LASTDATE(YearMonth[EndofMonth]);ALLSELECTED(YearMonth[YearMonth]))

produces the same issue as below and above

below is my exchange rate lookup:

Exchange Rate NOK = if(AX_LedgerTrans[Company Currency]="NOK";1
    ;LOOKUPVALUE(AX_ExchRates[EXCHRATE];AX_ExchRates[FROMDATE];[mMaxDate];
     AX_ExchRates[CURRENCY];AX_LedgerTrans[Company Currency])/100)

and here is my calculation of the amounts in the Group currency;

Amount NOK = AX_LedgerTrans[Exchange Rate NOK] * AX_LedgerTrans[AMOUNTMST]

And lastly the measure that calculates the total

NOK Actual corrected = CALCULATE(sum(AX_LedgerTrans[Amount NOK]);
  FILTER(all('YearMonth');'YearMonth'[Year] = values('YearMonth'[Year]) 
  && 'YearMonth'[EndOfmonth] <= MAX('YearMonth'[EndOfmonth])))

the measures above applies the exchangerate on:

the 31.01.2017 on the balancesheet transactions between 01.01.2017..31.01.2017

the 28.02.2017 on the balancesheet transactions between 01.02.2017..28.01.2017

the 31.03.2017 on the balancesheet transactions between 01.03.2017..31.03.2017

whereas what I really want is to apply the exchangerate on:

the 31.03.2017 on the balancesheet transactions between 01.01.2017..31.03.2017

I dont understand this.

[v-yulgu-msft]

Hi @fcastle,

Usually, to get the latest date value from a multi-selected slicer, we can use:

maxDate = MAX('TableName'[DateColumn])

I cannot understand the highlighted part "want the mMaxDate measure to simply return the latest date in my slicer while simultaneously ignoring the grouping per month" as you mentioned. 

For further analysis, would you please post some sample data and elaborate your report design so that I can test for you?

Best regards,
Yuliana Gu

[fcastle]

Hi @v-yulgu-msft

The thing is that all all variants like maxDate = MAX('TableName'[DateColumn]) abide by the relations, meaning that in essence this is what's actually happening:

mMaxDate = calculate(max(YearMonth[EndofMonth]);USERELATIONSHIP(AX_LedgerTrans[EndOfMonth];YearMonth[EndofMonth]))

What I want to do is to return the maxdate disregarding the relationship above. My calculated column:

Exchange Rate NOK = if(AX_LedgerTrans[Company Currency]="NOK";1
    ;LOOKUPVALUE(AX_ExchRates[EXCHRATE];AX_ExchRates[FROMDATE];[mMaxDate];
     AX_ExchRates[CURRENCY];AX_LedgerTrans[Company Currency])/100)

Returns the Exchange rate but still abiding by the mMaxdate using the active relationship. meaning it will return the exchange rate max(enddate) per month as opposed to the exchange rate for the last selected period.

BR

fcastle

[fcastle]

I will construct an example of this as I understand this isnt a vanilla request. It's highly likely that my implementation is flawed.

[fcastle]

Hi @v-yulgu-msft and @spuder,

Like I said, it’s likely my implementation that is a flawed.

I’ve selected cut-off 31.03.2017, which means that I want to sum all transactions for the year 01.01.2017..31.03.2017 and apply the exchange rate for period 31.03.2017 on all transactions, in order to get the value in the group currency.

Let’s take company B, who’s functional currency is EUR, and the exchange rate on the 31.03.2017 is 8.9, the figure I want powerbi to calculate is: 7770 * 8.9 = 69 153 whereas powerbi is still giving me a figure of 69 375, two figures which are explained here:

 

I've shared a pbi file that can be found here https://drive.google.com/open?id=0B8uTBZKEh4l6YmVONlREM0RaNDQ

Any help is greatly appreciated! 

BR

fcastle

[fcastle]

Hi all,

Should i interpret the silence as if this is non-solvable within the realm of PowerBI? I would have guessed me creating a pbi file https://drive.google.com/open?id=0B8uTBZKEh4l6YmVONlREM0RaNDQ illustrating the issue would render som feedback.

BR

fcastle

[spuder]

hi @fcastle

what i was able to create is a report that can calculate the right Amount NOK using the exchRate Table.

Is this what you wanted to create?

[fcastle]

@spuder, interesting, what happens if you filter on let's say the 31.03.2017?

could you send me the actual file?

BR

fcastle

[spuder]

@fcastle

try this link to get the file. I'm not sure if it works. If not give me a hint and I try something new.

https://1drv.ms/u/s!Agkuht1ucqk4gZJXrW9s1AQH3upw1w

[fcastle]

HI @spuder thanks for having a look at this!

I was able to download your file and your suggestions nudged me in new direction I've linked to a new file for which I'd like you to have a look at. I'm almost there, but unfortunately I still have a major flaw, and that's the exchange rate applied to the sum

I understand that his has to do with my exchange rate measure returning the max exchangerate filtered by date and currency code, but on the sum level the currency code is removed from the filter, thus the max exchange rate is returned. Problem is that what I really want is not have the sum be a product of the two measures, but rather the sum of the amounts (5550+69153+76923+3850) vs 23810*8,9

Any insights?

Link to updated file:

https://drive.google.com/file/d/0B8uTBZKEh4l6bzFLZzdRbGs4Z2M/view?usp=sharing

[spuder · Accepted solution]

@fcastle

Please change your Measure for mGroupAmount as follows

mGroupAmount = SUMX(Actuals;[mLocalAmount] * [exchange rate])

[fcastle]

OMG! @spuder Thank you very much!!!!!!!!!!! 

This was exactly what tied up the last issue!, and a bonus is that I finally understand the difference between Sumx and Sum...

Again, thank you for your time, effort and solution!

BR

fcastle