Forum Discussion

atrpbiuser's avatar
atrpbiuser
Frequent Visitor
4 years ago
Solved

Financial Calculations

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 , how...
Need Help
Tips and Tricks
  • atrpbiuser's avatar
    atrpbiuser
    4 years ago

    Following worked out. 

    Sales_Full_Month_LY = CALCULATE(SUM(sheet[INVOICE_SALES]),DATESMTD(ENDOFMONTH(DATEADD(Calender_Dates[Inv_Dates],-12,MONTH))))
amitchandak's avatar
amitchandak
Super User
4 years ago

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's avatar
    atrpbiuser
    Frequent Visitor
    4 years ago

    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's avatar
      atrpbiuser
      Frequent Visitor
      4 years ago

      Following worked out. 

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