Problem with calculating LY by financial calendar

[LeHoaiDuy]

I have a date table as below:

Start and end dates of the same month by Finalcial calendar can be different if years are different

How to calculate NSR LY , NSR MTD current month vs NSR MTD LY by Financial calendar in these cases?

Thanks,

[LeHoaiDuy]

Hi bro, I need to calculate NSR by LY based on my financial calendar not how to create a new one table

Thanks,

Duy

[some_bih]

Hi @LeHoaiDuy good if you have it. Please describe NSR by LY in more detail. LY is last year, what is NSR? Maybe I overlook something

[LeHoaiDuy]

Hi bro,

NSR = Net Sales Revenue

For example:  I have a Fact_sales table as below:

Please note that there may be a day without sales

I need to calculate NSR LY and MTD NSR of current month vs LY based on my financial calendar not Normal Calendar. SAMEPERIODLASTYEAR function will not work in this case because the start date of the financial calendar does not begin at the start of the month, and the end date does not align with month boundaries."

Date	Customer	NSR
1/1/2022	AB	15$
1/1/2022	AC	21$
2/1/2022	AB	12$
2/1/2022	AC	13$
2/1/2023	AB	15$
2/1/2023	AC	16$

[some_bih]

Hi @LeHoaiDuy possible solution, create following calculated columns (Start_Fiscal_Year, End_Fiscal_Year,Fiscal_Year) into Calendar table  and measures (NSR Measur, NSR YTD Measure, Total NSR, SPLY Fiscal NSR). Adjust Sheet21 to your table name

I used your and my dummy data, like data for customer XX

Start_Fiscal_Year =

VAR __start_of_fiscal_year =

    IF ( MONTH ( [Date] ) <= 2, YEAR ( [Date] ) - 1, YEAR ( [Date] ) )

RETURN

    DATE ( __start_of_fiscal_year, 3, 1 )

End_Fiscal_Year =

var __end_of_fiscal_year = if(month([Date])<=2,year([Date]),year([Date])+1)

return

if(mod(__end_of_fiscal_year,4)=0,date(__end_of_fiscal_year,2,29),ENDOFYEAR('Date'[date],"2/28"))

Fiscal_Year =

VAR __fin_year = year('Date'[Start_Fiscal_Year])

VAR __FY="FY "&FORMAT('Date'[Start_Fiscal_Year],"YYYY") & "-" &FORMAT('Date'[End_Fiscal_Year],"YYYY")

RETURN

__FY

NSR Measure = SUM ( Sheet1[NSR] )

NSR YTD Measure =

    TOTALYTD([NSR Measure],'Date'[Date])

Total NSR =

SUMX(

    FILTER(Sheet1, Sheet1[Date]>=MIN('Date'[Start_Fiscal_Year]) && Sheet1[Date]<=MAX('Date'[End_Fiscal_Year])),

    Sheet1[NSR]

)

 

SPLY Fiscal NSR =

    CALCULATE(

        [Total NSR],

        SAMEPERIODLASTYEAR('Date'[Date])

    )

Output

Did I answer your question? Kudos appreciated / accept solution!

[LeHoaiDuy]

I may do not get your solution. Ragarding Fiscal canlendar, EOM will not end at 28 or 29 of month, it can pull to next month, refer to March, June, September....

For full insight please see as below:

For now. I need to calculate Sales Revenue by:

• LY, % vs LY
• If current month has not ended, need to compare MTD vs MTD LY 

Hopefully, bro can give me a solution for this one

Thanks,

[some_bih]

Hi @LeHoaiDuy do you realy have Calendar / Date table according  created in Power BI?

In proposed solution, end of February is end / start for Fiscal year. What is start / end your fiscal year?

[LeHoaiDuy]

Hi bro, this date table is created by manually and only for reference. about date table on Power BI, i will upload by excel file & it will follow as fiscal calendar

Start date of fiscal year will be in 1 of Jan

End date of fiscal year will be in 31 of Dec

And NSR by month must follow fiscal month not normal month

Thanks,

Duy

[some_bih]

Hi @LeHoaiDuy year from 01.01.-31.12. is normal / defaul year so in your case fiscal year is the same as normal year?

If yes, what do you need to calculate? Simple SAMEPERIOD last year or ?

[LeHoaiDuy]

Hi bro, i used to use this function to calculate but it does not work. Please see below

Because fiscal month is non standard

[some_bih]

Hi @LeHoaiDuy for SAMEPERIODLASTYEAR to work properly, column/s for Date (year, monhs...) on visual should come from Calendar table, not fact table. Try it, if not already.

[LeHoaiDuy]

Hi, column year_monh i take it from date date table

[some_bih]

Hi @LeHoaiDuy for next steps, please share file.

[LeHoaiDuy]

 Hi bro, please see in attached file 

example LY test 

[some_bih]

Hi @LeHoaiDuy please check file v2.

On tab Page3 there is simple YTD, same period last year output

On tab Page4 there is fiscal year data with measures as I wrote earlier above.

Please check it and provide feedback.

Output

[LeHoaiDuy]

Hi bro,

Thanks for your solution, but

With date column in the table of page 3, I need to show data based on Fiscal Year & Fiscal Month not Normal Year_Month as you are using

In case, I use Fiscal Year & Fiscal Month for date column then data will show incorrrectly

Thanks,

Duy

[some_bih · Accepted solution]

Hi @LeHoaiDuy As I wrote, check Page 4 for Fiscal year data overview. On this tab there is Calendar Year (normal) and Fiscal Year in columns and there is SP measure.

[some_bih]

Hi @LeHoaiDuy I will check and let you know.

[LeHoaiDuy]

Hi bro, best thanks for your solution, the result is as my expactation

Additionally, could you help me with one more measure. How to calculate  MTD vs MTD LY?

Exp: Current fiscal month is just to 18 Oct 2023. So, MTD LY should only compare NSR from beginning of Oct 2022 to 18 Oct 2022

Thanks,

[some_bih]

Hi @LeHoaiDuy 

check link by our top solution author @amitchandak 

https://community.fabric.microsoft.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-a... 

Did I answer your question? Kudos appreciated / accept solution!