Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous,

 

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
Anonymous
Not applicable

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

 

What DAX formula did you end up using?

/sdjensen
Anonymous
Not applicable

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] ) ) )
Anonymous
Not applicable

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

 

 

Capture2.PNG

 

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

/sdjensen

@Anonymous- 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
Anonymous
Not applicable

I get the follow error:

 

Capture2.PNG

 

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

 

 

I made a mistake in my first post, so made an edit to the formula it should be "DATESBETWEEN( '01 Calendar'[Calendar Date], FirstDateLY, LastDateLY )" and not "DATESBETWEEN( '01 Calendar'[Fiscal Year Name], FirstDateLY, LastDateLY )" - I don't know if you copied my formula before I made this change - else please post the formula you are trying to use.

/sdjensen
Anonymous
Not applicable

Aha, i see. I did copy from the first post.
I have just left my computer - I am going to vacation.
I will be back 11 of December and I am looking forward to try the code.
Now we are in the end of the month, but do you think the current code will make the correct sum on a month basis? E.g. if we are having the 3rd of December the sum for last year will only show the sum for three days and not the whole month?

I hve struckled a lot with these time intelligence measures, and I end up with a lot of of if and Isblank calculations.

Okay - have a nice vacation.

 

The point of the final calculation should be that if the date is December 3rd, 2017 then the LY measure should only include last years values until December 3rd, 2016.

 

 

/sdjensen
Anonymous
Not applicable

Then I back, and I have implemented the new calculation.

However, I now get values in 1900 (no date/blank date) and 2008/2009 (my first year in the calendar table) 😞

 

Capture.PNG

Anonymous
Not applicable

I have created a power bi desktop model with same data as in my tabular model.

The model can be found here; https://www.dropbox.com/s/hp4j5ob6ahol9jx/DAX%20LY%20measure.pbix?dl=0

 

I cannot get the LY measure to work even though I have written it in the same way. I believe I need to mark my date dimension as date table. However, I am not sure how to do this in Power BI Destkop. 🙂

Anonymous
Not applicable

I think I got it solved - I added point within the if calulation;

COUNTROWS('Sales Transaction Details') > 0

 

 

 

Thus the final logik is as follow:

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'[Calendar Year Fiscal Name] ) && MIN( '01 Calendar'[Calendar Date] ) <= TODAY() && COUNTROWS('Sales Transaction Details') > 0,
//HASONEVALUE ( '01 Calendar'[Calendar Year Fiscal Name] ) && MIN( '01 Calendar'[Calendar Date] ) <= TODAY(),
CALCULATE (
[Revenue Before Bonus],
DATESBETWEEN( '01 Calendar'[Calendar Date], FirstDateLY, LastDateLY )
)
)

Anonymous
Not applicable

I tried to implement the same logik in my finance cub. However, I cannot get ride of the issue with value in 1900 and 2008/2009.

@sdjensen - can you think of other solutions to the problem?

 

The countrows > 0 logik does not solve it.

In my finans solution i have data from two facts; sum(GLTransactionsAgg[Amount DKK])+sum('Ledger Trans Details'[Amount DKK])

Transaction os a detailed level and on a aggregated level for years back in time.

 

However the below expression for the if statement does not solve the issue:

HASONEVALUE ( '02 Calendar'[Calendar Year Fiscal Name] ) && MIN( '02 Calendar'[Calendar Date] ) <= TODAY() &&

( COUNTROWS('GLTransactionsAgg') > 0 || COUNTROWS('Ledger Trans Details') > 0 ) ,


 

@Anonymous - Usually when I see these kind of issues it's either because the period table isn't fully populated or because all of the dates in your fact isn't in your Period table.

/sdjensen
Anonymous
Not applicable

Alright. Fair point. I will try to solve it at a later point. 🙂

 

Have you had issue with LY not showing a value, if there is no sales TY / current year?

 

Example:

I have a product that is sold in 2016/2017, but is NOT sold in 2017/2018.

For this product both Revenue and Revenue LY is blank 😞

 

 

Anonymous
Not applicable

I have figured out it is the last part I added that gives the issue.

The part "COUNTROWS('Sales Transaction Details') > 0"

 

I have tried to deleted it, but then I get the issue with values in the past.

@sdjensen - last time you said it could be cause the calendar/period table is not fully populated.

Most of your measures are following our fiscal calendar (starting in may).

Our first year in the calendar/period table is 2008. Would you suggest the perod should start 2008-01-01 or 2008-05-01?

 

Hope you can help 🙂

When working with time intelligence in DAX I would always make sure to have a full populated calendar year.

 

You are sure that there isn't dates in your fact table in the column with relationship to your calendar table, that has a date that is not included in your calendar table?

/sdjensen
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.