Get max date as chosen in dropdown

[MiKeZZa]

Hi all,

I have a demo PBIX here. In this PBIX I have a simple model; a fact with some dimensions. This is a really basic star schema.

Short question: I want the max date that is in the date dimension, based on the selected month (if user selected 2020 - 4 it must give 30-04-2020, if 2020 - 5 is selected it must give 31-05-2020.

But what I now experience is that using the MAX function (see my measure 'ProblemMeasure' which has this formula: MAX('Date'[Date])) on the date dimension is giving really bad performance. This because of that when I add more dim's I get a line for every possible combination of dimensions.

How can I make this faster?

Some more background info: There is a value in the fact, but this value is in Euro. I want the ability to 'translate' this to other currencies. The fact also has a currency and I would like to recalculate the euro amount based on a table with currencies. I can do that, but I need to have the selected period. If I can arrange to get the last date of the selected month I can pick the right exchange rate. But as stated before this give a sort of crossjoin which makes the measure REALLY slow when adding more dimensions or more records in a dimension.

[jdbuchanan71]

You could also write the measure this way so you don't have to add a column to your date table.

ProblemMeasureFixed = EOMONTH(CALCULATE(MAX('Date'[Date]),'Fact'),0)

[MiKeZZa]

Hi @jdbuchanan71,

First I thought you fixed it. But then I realised that my simple example is a little bit to simple. In my example it does work great. But I have 2 measures with TOTALYTD which more looks like this (by selecting whole 2020):

or this when selecting 1 month:

As you can see this doesn't work always because of that I get different months, because of there are cases where the last record in fact is not from the selectedmonth. So this is not working for every line in the report where the selected max month <> month of last record in fact table.

PBIX with this issue in it can be found here

So do you have a solution for this to?

[jdbuchanan71 · Accepted solution]

There were a couple problems in your example.  The Date table was not marked as the the model date table which will cause problems with time intelligence functions (TOTALYTD).  Your Cost Category measures were also not filtering the way you wanted I don't think.  I have updated the model and attached.

I also updated the problem measure to only return when the [YTD] measure I added has a result.


Also, Take a look at this article on implementing currency conversion in DAX.  It might help some down the road.
https://www.daxpatterns.com/currency-conversion/

[MiKeZZa]

Yes that's working great. It's causing some strange issues with it's speed in my larger dataset (when I work further with this date), but this is absolutely the right direction.

[jdbuchanan71]

The problem is there is no filtering flowing to limit which combinations get a result.  If you change the measure like so it should work.  It limits the result from the date table to be filtered by existing in the fact table.

ProblemMeasure = CALCULATE(MAX('Date'[Date]),'Fact')

[MiKeZZa]

Yes, I do understand. But that gives me the dates from the facttable. But when you choose (does not exists in my demo but can happen in real life) march 2021 I want the exchange rate of march 2021, not the date of the latest existing fact.

[jdbuchanan71]

If you add a column to to your date table like this

EOMonth = EOMONTH('Date'[Date],0)

Then you have your measure look at that column,

ProblemMeasureFixed = CALCULATE(MAX('Date'[EOMonth]),'Fact')

Does that give the result you are looking for?