how to divide this month over last year same month data

[PBI_newuser]

Hi,

when I calculate Oct 19 %, I want it to be ((C19/B19)/(C2/B2))-1.

how to set it as this month revenue divide by this month cost over last month revenue divide by last month cost?

besides, how to calculate Quarter data with the same logic?

[amitchandak]

@PBI_newuser , Convert this month to date

Date = "01 " & [Month Year]

Then you can use time intelligence with date table.

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]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))

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

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

[PBI_newuser]

Hi @amitchandak ,

How to calculate last year same month sales?

For example: Sales in Oct 19/ Sales in Oct 18

[Ashish_Mathur]

Hi,

You will have to create a Calendar Table and build a relationship with the Date column in your Data Table.  In the Calendar Table, write calculated columns to extract Year, Quarter, Month Name and Month number.  Sort the Month name by the month number.  To your visual, drag Year, Month name and Quarter from the Calendar Table.  Thereafter DIVIDE(), SAMEPERIODLASTYEAR() functions will help you.

To receive specific help, share the link from where i can download your PBI file.

[PBI_newuser]

Hi @Ashish_Mathur ,

Below is the sample data.

https://wetransfer.com/downloads/5a55acbe3f096deb07dd729f114597f720201008032949/728960f716f62c4578ea...

How to keep those % value from Oct 18 to Sep 19 (FY19) as no calculation in the column?

How to calculate the % value from Oct 19 onwards, by monthly, quarterly and yearly?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[PBI_newuser]

Thanks @Ashish_Mathur !! 
It works!

[Ashish_Mathur]

You are welcome.