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.
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
It seems like this formula is doing what you need then so you are all good.
Total Orders LY using MthIndex =
'Calendar'[MthIndex] >=MIN('Calendar'[MthIndex])-12 &&
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] )
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.