Financial templates vsLastYear make sameperiod

Power BI
21

Dummy-Data-File.pbix (156.8 KB)

Here’s a sample project to explain why I don’t believe I can use inbuilt time intelligence. Great if you can prove me wrong - hope you can :smile:

There is also another question within this file relating to banding - It’s all setup and working except for one column. Can you take a look at that as well please. More detail is in the file.

Thanks for your help so far

22

I’ve had a look through the file,

Now that I understand the scenario in more detail, yes the time intelligence formulas would not work here.

One thing…definitely start formatting your formulas better. The sooner you do this the quicker you will understand what DAX is doing within a formula.

For example I made some simple adjustments below.

image

Also placing information into a table is the best way to understand what is happening at every single results (I do this always). This way I can see the exact context on the calculation

image

It seems like this formula is doing what you need then so you are all good.

Total Orders LY using MthIndex = 
SUMX( 
    FILTER( ALL('Calendar'),
            'Calendar'[MthIndex] >=MIN('Calendar'[MthIndex])-12 &&
            'Calendar'[MthIndex] <=MAX('Calendar'[MthIndex])-12),
    [Total Orders])

I look into other ways to do something similar but this currently looks optimal.

You could use something like the below but not neccessary

Order LY = 
VAR CurrentYr = SELECTEDVALUE( 'Calendar'[YearIndex] )
VAR CurrentMth = SELECTEDVALUE( 'Calendar'[Fiscal Period] )

RETURN
SUMX(
    FILTER( ALL( 'Calendar' ),
        'Calendar'[Fiscal Period] = CurrentMth &&
        'Calendar'[YearIndex] = CurrentYr - 1 ),
    [Total Orders] )

Regarding the banding question…can you please place this in another thread.

Check out all the tips for using the forum below.

https://forum.enterprisedna.co/t/rules-and-tips-for-asking-support-questions-on-the-forum/30