topic Previous Month Last Year Sales - Card Visualisation in DAX Commands and Tips

Previous Month Last Year Sales - Card Visualisation

ClaireBear — Fri, 10 Jul 2020 11:03:44 GMT

Hi, I am trying to create 3 card visuals showing Previous Month Sales, Previous Month Last Year Sales and Year over Year % (Previous Month vs Previous Month Last Year %). I will then attempt the same calculations for Before Previous Month to. My Previous Month Sales calculation works when i select the drop down year, but i cannot get the Previous Month Last Year Right. Current Dax: Previous Month Sales = var current_month= MONTH(TODAY()) return CALCULATE(sum('Fact Basket'[BasketTotal]),FILTER('Fact Basket',MONTH('Fact Basket'[TransactionDateTime])=current_month -1)) Is anyone able to assist? Thank You

Re: Previous Month Last Year Sales - Card Visualisation

AntrikshSharma — Fri, 10 Jul 2020 14:04:32 GMT

Try this:

Previous Month = CALCULATE ( [Total Sales], PREVIOUSMONTH ( Dates[Date] ) )

Previous Month LY = CALCULATE ( [Total Sales], PREVIOUSMONTH ( SAMEPERIODLASTYEAR ( Dates[Date] ) ) )

YOY % = DIVIDE ( [Previous Month] - [Previous Month LY], [Previous Month LY] )

Re: Previous Month Last Year Sales - Card Visualisation

amitchandak — Fri, 10 Jul 2020 15:43:37 GMT

ClaireBear , Try these with a date calendar. do You need 13 months behind measure , then use 13 in place of 12

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 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))

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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

Appreciate your Kudos.

Re: Previous Month Last Year Sales - Card Visualisation

ClaireBear — Thu, 16 Jul 2020 12:25:37 GMT

This worked great! I used the following measure for Quarter:

Previous Qtr LY Sales = CALCULATE([total sales],DATESQTD(ENDOFQUARTER(dateadd('Dimension Time'[Date],-5,QUARTER))))

Thank You!

Re: Previous Month Last Year Sales - Card Visualisation

abc_777 — Sun, 21 Nov 2021 09:16:51 GMT

Previous Qtr LY Sales = CALCULATE([total sales],DATESQTD(ENDOFQUARTER(dateadd('Dimension Time'[Date],-5,QUARTER))))

can this calculation works in CARD visual?

it works in table matrix but not as card visual. pls help