Calculate growth between CY and PY Sales

[Anonymous]

Hello,

I'm quite new to Power BI, making a transition from Excel. For a sales report that I'm developing in Power BI, I was wondering if it's possible to show Current Year MTD, YTD Sales and compare with PY-MTD, PY-YTD figures and then calculate the growth % between the Sales numbers of CY and PY. The Sales numbers for both the years are in my Fact table. 

Many thanks in advance for your help.

Cheers,

S.

[nandukrishnavs]

@Anonymous 

It is possible. But without seeing your data, it is difficult to share any formula. 

Check out these links. https://www.sqlbi.com/blog/marco/2018/08/10/the-hidden-secrets-of-totalytd/

https://powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

Hi @nandukrishnavs,

Thank you for the response. Here is a randomized sample of my data for the reference.

Thanks,

S.

[amitchandak · Accepted solution]

@Anonymous , Your sample is image . How would someone use it.

Try time intelligence with Date table

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

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

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
last QTR same Month (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,Qtr))))
MTD (Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR('Date'[Date])))
MTD (Last Year End) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFYEAR(dateadd('Date'[Date],-12,MONTH),"8/31")))

diff = [MTD Sales] - [last year MTD Sales]

change % = divide( [MTD Sales] - [last year MTD Sales], [last year MTD Sales])

[Anonymous]

@AM, Thank you for the response.

My bad, I just realised that I uploaded an image file instead of the sample data file as they both had the same file name 😞

However, thank you for posting the time intelligent formulas! They seem to not work at first, but then I realized after reading a few other posts that I need a calendar with contigous dates, and after I created the relationship, I was able to create the measures for YTD, PYTD, PYMTD.

Thanks again.

BR,

S.