Getting extra month with YEAR & MAX formula

[Nerdopolis]

Hello,

Having an issue with what seemed to be a simple formula involving DAX YEAR, MAX, and a hierarcheal Calendar table.  Using a Clustered Column Chart, it was easy to show a monthly annual comparison.  X-axis = month, Y-axis = Revenue, Legend = year.

Since my company is only interested in the current & previous year comparison,  I have a simple DAX expression to limit the visual.

    Measure = CALCULATE (sum (Data[Revenue]), YEAR (Data[Date]) > YEAR(MAX(Data[Date]))-2)        //This works

As soon as I try to fold in the Calendar table (best practice?), I get the following;

Tried . . . 

    Measure = CALCULATE (sum (Data[Revenue]), YEAR (Calendar[Date]) > YEAR(MAX(Calendar[Date]))-2)   

    Measure = CALCULATE (sum (Data[Revenue]), YEAR (Calendar[Date]) > YEAR(MAX(Calendar[Date].[Date]))-2)

    Measure = CALCULATE (sum (Data[Revenue]), Calendar[Date].Year > MAX(Calendar[Date].[Year])-2) 

NOTE: Using a different sum (such as Income instead of Revenue) SOMETIMES works with the ".[Date]" addition (which makes sense since it is accessing the hidden calendar hierarchy table)

Another NOTE: All revenue dates are month end (i.e. maybe Dec 31, 2020 00:00:00AM is really 2021???)

Guess I don't really understand the PowerBI date tables.  If I mark the Calendar table as a "date table", I lose the hierarchy and thus "January" becomes "1" (not as pretty).

Could just not use the calendar table but my curiosity is piqued.  Any suggestions or best practices would be appreciated.

Thanks,

Shawn

[speedramps]

Thanks Nerdopolis.

It is a common requirement to use a business date instead of today's date to calculate the daily, weekly, monthly and yearly offsets.

There are very methods to archeive this in your tweak ...

1) If you have a system table with the buisness date them use that.

2) Or get the max date from the fact table and assume that is the most recent date, and use that one.

Please do any free youtube Power BI Calendar training training especially how to get the start, end and business dates and the offsets.

All Power BI reports use dates, so it is important to learn about a Calendars tables !

Learn how to build a Calendar once and use always, rather than try write DAX logic each time.

Raise a new ticket and quote @speedramps in the text if you need more help.  I will receive an automated notification.

[speedramps · Accepted solution]

Dont use DAX for date logic.

Use a calendar table instead with offsets and then ....

Thisyearqty= CALCULATE( SUM( sales[qty]),  Calendar[yearoffset] = 0)
Lasyyearqty = CALCULATE( SUM( sales[qty]),  Calendar[yearoffset] = -1)

Please do all this free Power BI Calendar training training especially the offset lesson.

All Power BI reports use dates, so it is important to learn about a Calendars tables.

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[Nerdopolis]

Thanks!  That led to a solution.  May need to tweak further as our data is not realtime (i.e. don't want to roll over visuals until January data is loaded).  But very much appreciated!