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.
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.
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) 😞
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. 🙂
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] ) ),
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(),
[Revenue Before Bonus],
DATESBETWEEN( '01 Calendar'[Calendar Date], FirstDateLY, LastDateLY )
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 ) ,
@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.
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?
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 😞
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 🙂
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!
Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications