Financial Calculations

[atrpbiuser]

Hi -

Trying to replicate a Tableau report to PBI and need some direction.

We have a Sales_Date column inside the main SQL query through which we are trying to pull sales for different periods , however it didn't work out so built a static date table which is joined to the main table as One to Many and now the basic period's calculations like MTD,YTD, LY_MTD,LY_YTD are working fine using time intelligence functions. However we are struggling with 2 calculations "Sales Full Year LY" and "Sales Full Month LY" .

For example sales through 8/25/21 works fine with DATESMTD() although couldn't get to find a function to pull "Sales Full Month LY" which will show sales for entire August Month from last year even though the August month is not complete yet which is why couldn't use SAMEPERIODLASTYEAR() . Any help will be greatly appreciated. 

Thank You.

[amitchandak]

@atrpbiuser , use previousmonth and previousyear

last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))

last Year Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))

Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

[atrpbiuser]

Thank you very much @amitchandak -

Will last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))  return full month's sale from last year ?

For example - today is 8/30/21 and still has one day to close the month , what we are looking to display is last year August's full month sales from 8/1/2020-8/31/2020 alongside the current MTD (8/1/2021-8/30/2021) sales .

Thank you very much for your assistance. 

[atrpbiuser · Accepted solution]

Following worked out. 

Sales_Full_Month_LY = CALCULATE(SUM(sheet[INVOICE_SALES]),DATESMTD(ENDOFMONTH(DATEADD(Calender_Dates[Inv_Dates],-12,MONTH))))