Calculate previous year sales with fiscal month id

[renjithpbi]

Hi All, 

I have a fiscal caledar where the Months starts from 1st sunday of everymonth and below is the sample calendar table:

Here, In 2023 fiscal month Jun starts from 4th Jun and ends by 1st Jul.

and,   In 2024 fiscal month Jun starts from 2nd Jun and ends by 29th Jun.

I wanted to create a measure for Sales Last Year, where it can compare CY fiscal month vs LY fiscal month.

I have created a dax to calculate 

Sales LY = CALCULATE([Net Value Online], DATEADD(DIM_FISCAL_CALENDAR[Date], -1, YEAR))

This gives result, But the problem is, it calculates the sales from 2nd Jun 2023 to 29th Jun 2023. 

I am expecting that it should calculate from 4th Jun 2023 to 1st Jul 2023.

Any ideas?

Thanks

Ranjith

[vanessafvg]

try sameperiodlastyear() instead.

https://learn.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

[renjithpbi]

I tried, but it does not give the rusult I wanted. 

[v-fenling-msft]

Hi, @renjithpbi 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[renjithpbi · Accepted solution]

Yes, I was able to resolve it.

1. I created a calc column from FMNTH_ID like below,

Curr Month PY = date(LEFT(DIM_FISCAL_CALENDAR[FMTH_ID], 4)-1, right(DIM_FISCAL_CALENDAR[FMTH_ID], 2), 1)

2. Changed the format Date-2001-03 (yyyy-mm)

3. Created a measure like this 

Sales LY =

VAR prevMonth =    MAX ( DIM_FISCAL_CALENDAR[Curr Month PY])

RETURN

CALCULATE([Net Value Online], DIM_FISCAL_CALENDAR[Curr Month] = prevMonth, ALL(DIM_FISCAL_CALENDAR))

This worked!

Thanks for the support. 🙂