Prior Year Sales by Month Based on Financial Month

[Anonymous]

Hi All,

I am new to the forum so let's try this 🙂

I have been using DAX for some time now but I am struggling with this scenario:

The client's Financial Months run from the 25th to the 26th in the next month, as example the month of April sales will run from 25 March to 26 April.

This I managed to group together to aquire the correct sales data but when I start comparing to Prior Year and Prior 2 Years using the DATEADD formula it gives error stating that the dates need be in continuous flow; hence starting from the 1st till the last day of the month in question.

The figures will change based on the financial year chosen in the filter.

I hope this was enough information.

Please can someone help?

[Ashish_Mathur]

Hi,

Share some data and show the expected result.  Also, what is their Financial year?

[v-piga-msft]

Hi @Anonymous ,

I still have a little confused about your scenario. Please let me know more details.

Do you have a Calendar table? Is your date continuous?

If it is convenient, could you share some data sample which could reproduce your scenario and your desired output so that we could help further on it? Or, ideally, share the pbix (beware of confidential data)

Best Regards,

Cherry

[Anonymous · Accepted solution]

Hi @v-piga-msft @Ashish_Mathur ,

I managed to get it right last night I created a few calculated coloumns below is how I managed to get it right>>

1. I created a new grouping months column based on date to get the financial months in order

Financial Month =
FORMAT (
DATE ( 2010, IF (
MONTH ( [Date] ) = 2,
MONTH ( [Date] ),
IF (
AND ( DAY ( [Date] ) > 25, DAY ( [Date] ) <= 31 ),
MONTH ( [Date] ) + 1,
MONTH ( [Date] )
)
), 1 ),
"MMMM"
)

2. I created a month sort so that the months are aligned from March to Feb

=VAR FYStartMonth = 3 //Update the fiscal year starting month above *Use number between 1 to 12
RETURN
IF (
IF(
AND(DAY([Date])>25,DAY([Date])<=31),MONTH([Date])+1,MONTH([Date])) >= FYStartMonth,
IF(
AND(DAY([Date])>25,DAY([Date])<=31),MONTH([Date])+1,MONTH([Date])) - ( FYStartMonth - 1 ),
12
+ (
IF(
AND(DAY([Date])>25,DAY([Date])<=31),MONTH([Date])+1,MONTH([Date])) - ( FYStartMonth - 1 )
)
)

Then it worked perfectly, was a bit tricky but yes works great with the SAMEPERIODASLASTYEAR or even PARALLELPERIOD formula.

Very tricky but yes Power BI once again succeeds!

Thank You