Calculate Sales based on Fiscal Month and Calendar Month

[SridharAkidi]

Can any one explain how to Calcualte total sales by fiscal month and calendar month?

I have Sales Table which is at Date level and Date table which has both Calendar Month and Fiscal Month.

[amitchandak]

@SridharAkidi , If fiscal month is not the same as a calendar month

This month last month Calendar

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

FY Month/Period

You need a new column on YYYYPP or period start Date

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)

measure
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

Last Year Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period]=max('Period'[Period]) && 'Period'[Year]=max('Period'[Year])-1))