cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Solution Sage
Solution Sage

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

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

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

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

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

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

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

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

Highlighted
Helper III
Helper III

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

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. 🙂

Highlighted
Helper III
Helper III

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

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 )
)
)

Highlighted
Helper III
Helper III

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

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 ) ,


 

Highlighted
Solution Sage
Solution Sage

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

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

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

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 😞

 

 

Highlighted
Helper III
Helper III

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

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 🙂

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors