Forum Discussion

ClaireBear's avatar
ClaireBear
Icon for Helper I rankHelper I
6 years ago
Solved

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
Previous Month Last Year Card

4 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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.

    • ClaireBear's avatar
      ClaireBear
      Icon for Helper I rankHelper I
      6 years ago

      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!
      • abc_777's avatar
        abc_777
        Icon for Solution Specialist rankSolution Specialist
        4 years ago

        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