R12 Data This year and last year

I am trying to perform some R12 calculations.

I have 24 months of data and change this every month.

Currently the range is 01.07.2019 - 30.06.2021.

I have the reports I want in excel and trying to recreate them in Power BI - this is useful as I can sense check them.

I have tried to use the attached formula that was from a previous post from Melissa

This does not give me the answer I am expecting. I have used the date tables provided by Enterprise DNA so know these are OK.

Once I have this resolved is it possible to then have a formula for the prior R12 month… I guess this must be easy.

I am really finding the shift from Excel to POWER BI incredibly difficult but feel it is really worth the pain - DAX feels like a completely different thing to excel formula and despite being pretty good in excel I feel like an amoeba in Power BI

Thanks for your consideration.

Please also include your Power bi file.

Hi @NeilR, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing

  • A mockup of the results you want to achieve

  • Your current work-in-progress PBIX file

  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

I have loaded the file now.

I thought I had done this yesterday but that was clearly not the case - apologies

Hi @NeilR,

First I’ve marked your Dates table as a Date table. Next give this a go:

Rolling 12M = 
CALCULATE( [Net Sales],
    DATESINPERIOD( 'Dates Table'[Date], MAX( 'Dates Table'[Date] ), -12, MONTH )
)

Rolling 12M LY = 
CALCULATE( [Rolling 12M],
    SAMEPERIODLASTYEAR( 'Dates Table'[Date] )
)

If this is not the result you are looking for please provide a mockup with expected results, thanks
I hope this is helpful

2 Likes

Melissa,

This is perfect. It works just as I want to thanks so much for that.

I am struggling to understand why it works but this I suppose is due to my poor understanding of dax.

In Excel, there is the drop down box when you build a formula up that helps you understand the steps.

Is there anything similar in Dax or is it just trying to understand the language?

Hi @NeilR,

Glad to hear that worked well for you!

I think you will appreciate the Accelerator that’s currently being hosted by @BrianJ on eDNA. This weeks round ( #3 ) will probably shet some light on why and how things work in DAX. If you haven’t joined in this initiative, I highly recommend it.

. You can also revisit previous rounds, solution video’s and other content are here in the portal.

I hope this is helpful

1 Like

@NeilR ,

I wholeheartedly endorse Melissa’s recommendation of Power BI Accelerator as an initiative that will definitely help you build up your DAX skills and understanding.

Two other suggestions:

DAX Studio (one of the essential External Tools for Power BI) recently added a feature called Query Builder, which lets you build out DAX queries interactively. Even better, there’s a window at the bottom of the screen that visualizes the result of your query. I find this window into visualizing what DAX is doing in real time is invaluable in developing a strong understanding of DAX. Here’s a recent introduction that SQLBI did into the Query Builder function, plus a recent GIAC video on the same topic:

Also, here’s a video I did a while back on strategies for improving your DAX skill that I think you might find helpful:

And here’s Patrick from GIAC’s strategy on learning DAX:

Finally, I would just encourage you to “keep pounding the rock”. Progress on DAX is not linear. Instead, it often looks like no progress, no progress, no progress, then BAM! - lightbulb clicks on and you make a huge leap forward. Then back to no progress, no progess, and the BAM! again. By applying some of the techniques above and speeding your progress with Power BI Accelerator, you will still have those plateau periods, but they will become shorter and shorter over time.

I hope this is helpful.

  • Brian
1 Like

Thanks, Brian,

This is very helpful. I hope that light bulb moment comes sooner rather than later!

I will look at the accentuator courses too

1 Like

Is this the download that you are talking about, Brian?

1 Like

NeilR,

Indeed. One caveat - DAX Studio is an incredibly powerful tool with a lot of complex capabilities. At this point in your learning process, don’t worry about learning that other functionality - much of it has to do with optimizing DAX and your data model. I’m a firm believer in “make it work, THEN make it fast”. At this point, you’re just at the “make it work” phase. The DAX Query Builder will be the primary reason you’d be using DAX Studio for now.

  • Brian
1 Like

Hi Brian,

Am i able to remove the data model from the thread now please as this is sensitive data

Thanks

1 Like

@NeilR ,

No worries – I just removed it for you.

For the future, just note that we have some excellent tools and techniques available for free to members and nonmembers that you can use to mask any confidential data in your PBIX so that it can be posted to the forum with no concerns.

https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763

  • Brian
1 Like

fabulous thanks Brian, Will bear that in mind for future data loads.

Thanks for the removal

1 Like