Compare sales monthly total to current month average and LY same month average

[Daming-Song]

I am trying to creat a bar chart to have each sales' monthly total sale amonut compare current month average and same month last year's average.  Any help is appreciated! 

Sales	Date	Amount
A	1-1-2020	100
B	1-2-2020	50
C	1-2-2020	150
D	1-3-2019	50
E	1-2-2019	80
F	1-1-2019	50

[amitchandak]

@Daming-Song , you can use time intelligence and date 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]))
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)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))


Last year same month value =  CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-12,MONTH)))

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[parry2k]

@Daming-Song As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

Once you have date dimension, create a measure for average and use SAMEPERIODLASTYEAR function to get the previous year average, and you will be good to go. Key is to add date dimension in the model.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Daming-Song]

Thank you for your help!   I have the measure for current month average and same month last year's average.  On my bar chart, I put sales as the Axis, sale's monthly sale total and Current month average and same month last year average as the value.  The bar chart only shows sale's monthly total and current month average, the same month last year average does not show up.  Any solution for this? 

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file and show the expected result.