cancel
Showing results for
Did you mean:
Highlighted
Helper III

## DAX measure - Last Year measure that only shows values until today's date

Hi all,

I want to be able to compare Revenue TY (this year) with Revenue LY (last year). I want to be able to compare these measures both on a day, month and a year level. Thus, I do not want to show any future values for last year, even though these actually exist. As shown below I do want to get ride of the red numbers, and I want to ensure the the sum is correct in the yellow marked fields.

In the below picture todays date is 27-11-2017 (and we did not have any sales 27-11-2016, thus no values is shown for LY)

Can somebody help?

I have tried with some complex dax, but I figured out it dont work as intended. Thus I have started again with the simple calculation;

`CALCULATE ( [Revenue Before Bonus], SAMEPERIODLASTYEAR ( '01 Calendar'[Calendar Date] ) )`

20 REPLIES 20
Highlighted
Solution Sage

Hi @linekrogh,

You could try a calculation like this: (I assume you have a year column in your '01 Calendar' Table)

```IF (
HASONEVALUE ( '01 Calendar'[Calendar Year] ) && MIN( '01 Calendar'[Calendar Date] ) <= TODAY(),
CALCULATE (
[Revenue Before Bonus],
SAMEPERIODLASTYEAR ( '01 Calendar'[Calendar Date] )
)
)```
/sdjensen
Highlighted
Helper III

Hi @sdjensen,

I tried - I did almost work.

However, I do not get any any values at a year level. I would like to have the sum of 20.950.725 shown at 2017/2018.

Any suggestions?

Highlighted
Solution Sage

What DAX formula did you end up using?

/sdjensen
Highlighted
Helper III

The one you mentioned - onyl change is that my year is called calendar year name.

`IF ( HASONEVALUE ( '01 Calendar'[Calendar Year Name] ) && MIN( '01 Calendar'[Calendar Date] ) <= TODAY(), CALCULATE ( [Revenue Before Bonus], SAMEPERIODLASTYEAR ( '01 Calendar'[Calendar Date] ) ) )`
Highlighted
Helper III

The Calendar Year Name includes the below values (string):

Highlighted
Solution Sage

You should use the column that you have in your period hierarki - I can see it's not following the calendar year.

/sdjensen
Highlighted
Helper III

Aha, I get it.

Now I have changed it to the same one as in my calendar.

However, I do not get the right sum at the year level.

The sum for year 2017/2018 is currently looking at december 2017-april 2018, even though the numbers are not shown.

Thus, I would like to get the sum to 20.950.671 so I am able to compare it with the number for the current year (19.503.653).

I now it is tricky. Any suggestions?

Highlighted
Solution Sage

@linekrogh- I have tweeked the calculation a bit. First of all as I said in the previous post, you need to use the column used as you year column in your hierarki in the HASONEVALUE function and not your year column. Secondly there is an issues with using SAMEPERIODLAST year, because on the year total it will return the full value on last year and not last year until today - please try the following syntax:

```Revenue Before Bonus LY =
VAR FirstDateLY = FIRSTDATE ( SAMEPERIODLASTYEAR( '01 Calendar'[Calendar Date] ) )
VAR TodayLY = DATE( YEAR( TODAY() )-1, MONTH( TODAY() ), DAY( TODAY() ) )
VAR LastDateLY = IF(
LASTDATE( SAMEPERIODLASTYEAR( '01 Calendar'[Calendar Date] ) ) < TodayLY,
LASTDATE( SAMEPERIODLASTYEAR( '01 Calendar'[Calendar Date] ) ),
TodayLY
)

RETURN
IF (
HASONEVALUE ( '01 Calendar'[Fiscal Year Name] ) && MIN( '01 Calendar'[Calendar Date] ) <= TODAY(),
CALCULATE (
[Revenue Before Bonus],
DATESBETWEEN( '01 Calendar'[Calendar Date], FirstDateLY, LastDateLY )
)
)```
/sdjensen
Highlighted
Helper III

I suppose you are danish, so you probably understand it 🙂

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors