Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ClaireBear
Helper I
Helper I

Previous Month Last Year Sales - Card Visualisation

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
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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-Y...


Appreciate your Kudos.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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-Y...


Appreciate your Kudos.

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!

hi 

 

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
AntrikshSharma
Community Champion
Community Champion

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

P.PNG

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors