Compare most recent June to previous June

Data Table

Date Value
30-Jun-19 10
1-Jun-20 15

I have month level granularity data and want to compare the most recent June to the previous June. The date is different from 2019 to 2020.

Any ideas how I might achieve this?

Any ideas what function I should be looking at?

I have looked at so many functions and maybe its the end of the week, but I am having no luck.

In english like code, I suppose I want (even though there is only one value as it is month level data), I want to SUM the Data[Value] for the MAX June month in my date calendar.

After that I can use SAMEPERIODLASTYEAR, or DATEADD, and things like that to compare to 2019.

Any guidance most appreciated.
Thanks
John

@jgriffit,

I have a number of ideas. If you could please upload some sample data, that would help me develop/test a specific solution for you.

Thanks.

  • Brian

Hi Brian,
Please find attached a data file. Thanks for thinking about it.

I tried something like

Measure = CALCULATE(SUM( Data[Val] ), YEAR( ‘Calendar’[Date]) = MAX(‘Calendar’[Year]), ‘Calendar’[Month Name] = “June” )

But that fails as I still not great at the DAX. But you can get the intent from the poor code above.

I was looking at
EOMONTH, but that is just 30/6,
CLOSINGBALANCE but didn’t know what I was doing :wink:

perhaps a filter DATESMTD would get the latest month and then use last year?

enda data.pbix (83.7 KB)

Thanks Brian

@jgriffit,

See how this works for you. In these types of very specific scenarios, rather than using canned time intelligence functions, I like to “roll my own”, since I think it provides better clarity into what’s going on.

You had exactly the right idea in the first approach you tried in the post above - it just needed some refinement in the DAX.

I started with measure to find the max year for any selected month:

Max Year for Selected Month = 

CALCULATE( 
    MAX( 'Calendar'[Year] ),
    FILTER(
        ALLSELECTED( 'Calendar' ),
        'Calendar'[Month Name] = [Harvest Month Name]
    )
)

Then using that, calculated the total value for selected month and max year for selected month (using the measure above):

Total Value SelMonth Max Year = 
CALCULATE(
    [Total Value],
    FILTER(
        'Calendar',
        'Calendar'[Month Name] = [Harvest Month Name] &&
        'Calendar'[Year] = [Max Year for Selected Month]
    )
)

From there it was simple to calculate the total value for the selected month, previous year just by sticking a “- 1” on the second filter condition of the measure above:

Total Value SelMonth Max Year -1 = 
CALCULATE(
    [Total Value],
    FILTER(
        'Calendar',
        'Calendar'[Month Name] = [Harvest Month Name] &&
        'Calendar'[Year] = [Max Year for Selected Month] - 1
    )
)

Here’s what it looks like all put together:

I hope this is helpful. Full solution file attached below.

2 Likes

This was great Brian. Roll your own made me laugh.

I very much appreciate you not using a time intelligence out of the box and showing the way to build the custom answer and ‘behind the curtain’.

I had to understand ALLSELECTED again, and open the pbix to understand what [Harvest month] was but all understood now.

The -1 was clean. I would have added SAMEPERIODLASTYEAR or similar out of the box function, but again, roll your own, keep it simple and clean and understandable. A thing of beauty.

Practice practice practice is what I need, and I thank you for your experience, patience and time.

Kind regards
John

@jgriffit,

Great - glad that was helpful. This is one of the awesome things about the forum - questions not only lead to answers for the initial poster (and anyone else with a similar question), but they also serve as one of the best means of practice for those answering questions. And regular practice is really the only way to improve your DAX once you have the basics down.

The other best form of practice is the data challenges. If you really want to improve your DAX (and data modeling, and power query and visualization…) quickly, the challenges really represent the best investment of practice time IMO.

Here’s a video I put together on the specific strategies I’ve found most useful for improving DAX (and M) skills that you may find helpful in structuring your own practice routine:

Re: time intelligence, everyone develops their own DAX style and approach. Some folks on the forum are masters of the entire lexicon of time intelligence functions. I’ve taken the opposite approach -pretty much all I know and use are: DATEADD, DATESBETWEEN, DATEDIFF, TODAY and occasionally EOMONTH. Combine this with @Melissa’s awesome extended date table and a knowledge of how to use the offsets in that table:

and you’ve pretty much got everything you need in 4-5 functions.

Hope this is helpful. Always a pleasure working with you.

  • Brian

Thanks so much for the extra information Brian. I will watch with interest.
I recently came across a little watched video series by Brian Grant called Elements of DAX. He has a unique excel based visual approach to ‘bridging the gap’ that many like myself found hard to grasp. Watching his older videos shows he has continuously developed this approach and I now believe he has hit a sweet spot, and it really struck a cord with me. Perhaps as I am a very visual learner. I think you would find interesting. Google / youtube search for those terms and you will find. His next series is out in about a month (he advised).
Regards
John

@jgriffit,

Thanks for the info. I’ve not seen this series, but will check it out. Funny thing is that I’d never heard of him before until earlier in the week, when in the Power BI Weekly newsletter, my most recent video got mistakenly attributed to him…

  • Brian

Wow, what a coincidence !