cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
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)

Capture.PNG

 

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
Solution Sage

Re: LAX measure - Last Year measure that only shows values until today's date

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
Helper III

Re: LAX measure - Last Year measure that only shows values until today's date

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?

 

Capture2.PNG

 

Solution Sage
Solution Sage

Re: LAX measure - Last Year measure that only shows values until today's date

What DAX formula did you end up using?

/sdjensen
Highlighted
Helper III
Helper III

Re: LAX measure - Last Year measure that only shows values until today's date

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
Helper III

Re: LAX measure - Last Year measure that only shows values until today's date

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

 

 

Capture2.PNG

 

Highlighted
Solution Sage
Solution Sage

Re: LAX measure - Last Year measure that only shows values until today's date

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
Helper III

Re: LAX measure - Last Year measure that only shows values until today's date

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?

 

 

Capture2.PNG

Highlighted
Solution Sage
Solution Sage

Re: LAX measure - Last Year measure that only shows values until today's date

@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
Helper III

Re: LAX measure - Last Year measure that only shows values until today's date

I get the follow error:

 

Capture2.PNG

 

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

 

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors