topic Create a measure as last value of another measure for the year in DAX Commands and Tips

Create a measure as last value of another measure for the year

JOKA — Wed, 27 May 2020 17:56:28 GMT

Hi!

I need your help. I have "Measure A", then I created "Measure B" and finally "Measure C". The problem is Measure B. I need that this measure takes the value of the last month available for Measure A, then divide this value by 12 and then asign the result to the whole year.

In the following image I show you how I would like to see it:

The dax is like this:

- Measure A is ok

- Measure B = CALCULATE(DIVIDE([Measure A];12);ALL('Date'[Fiscal Month]);'Date'[Fiscal Month Nr]=7)

- Measure C = CALCULATE([Measure B]*[Month Nr])

The last month available is Fiscal Month Nr = 7. I am filtering manually this data in Measure B by adding the last filter ('Date'[Fiscal Month Nr]=7), but I need this to be dinamic and change to the following month (number 😎 every time I load new data.

I can´t find a way to solve this. I have tried by adding a new column in my Date table to identify the last month with data but it doesn´t work in a calculate function.

Does anyone know what can I do?

Thank you!!

Re: Create a measure as last value of another measure for the year

mahoneypat — Thu, 28 May 2020 02:29:28 GMT

Please try this expression for your measure. This assumes your Fiscal Month Number column is numeric (you could convert that in the formula if text), and you've made a table visual with Fiscal Month Number, Month, and this measure.

Measure C =
VAR maxfiscalmonth =
CALCULATE (
MAX ( Fiscal[FiscalMonthNumber] ),
FILTER (
ALL ( Fiscal[FiscalMonthNumber] ),
NOT ( ISBLANK ( CALCULATE ( [Measure_A] ) ) )
)
)
VAR maxmonthvalue =
CALCULATE (
SUM ( Fiscal[Measure A] ),
ALL ( Fiscal[FiscalMonthNumber] ),
Fiscal[FiscalMonthNumber] = maxfiscalmonth
)
VAR result =
SELECTEDVALUE ( Fiscal[FiscalMonthNumber] ) * maxmonthvalue / 12
RETURN
result

If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Create a measure as last value of another measure for the year

JOKA — Thu, 28 May 2020 14:13:26 GMT

Hi!

Thank you for your response! I tried this but didn´t work. Here is what I see now ("Measure D" is the one with the new expression):

Measure D =

VAR maxfiscalmonth =

CALCULATE (

MAX ('Date'[Fiscal Month Nr]);

FILTER (

ALL ('Date'[Fiscal Month Nr]);

NOT ( ISBLANK ( CALCULATE ('Measures 1'[Measure A]) ) )

)

VAR maxmonthvalue =

CALCULATE (

'Measures 1'[Measure A];

ALL ('Date'[Fiscal Month Nr]);

'Date'[Fiscal Month Nr] = maxfiscalmonth

)

VAR result =

SELECTEDVALUE ('Date'[Fiscal Month Nr]) * maxmonthvalue / 12

RETURN

result

Re: Create a measure as last value of another measure for the year

mahoneypat — Fri, 29 May 2020 00:03:59 GMT

I didn't have the Month name in my visual, so I didn't add that column to the All() parts. If you replace the two All( ) with this, it should work

ALL ('Date'[Fiscal Month Nr], 'Date'[Month])

If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: Create a measure as last value of another measure for the year

JOKA — Wed, 03 Jun 2020 17:43:08 GMT

Now it works! Thank you for your help!