Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Current Month Measure divided by previous month figure

Good morning - 

I have a table with data inputs below.  I am struggling to come up with a measure for the "Desired Result" column.  The desired result is current month AR minus current month sales divided by PREVIOUS month's sales.  So for instance, in February 2021, my desired result would be $4,000 (current month AR) - $2,000 (current month sales) / $1,000 (previous month sales) = 2. 

 

 

I have created a measure called "AR Less Sales" which accomplishes the first part of my desired result (current month AR - current month sales). I am struggling to come up with the second part of my desired result which divides my "AR Less Sales" measure by the PREVIOUS month sales. 

 

I have tried: 

Desired Result = CALCULATE([AR Less Sales]/[Sales Historical](PREVIOUSMONTH('Date'[YearandMonth])))

Desired Result = [AR Less Sales]/[Sales Historical]('Date'[YearandMonth],-1)

 

Can somebody please assist? Any help or advice would be greatly appreciated.

 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi Anonymous 

    Try this measure to get the result. In my example, [YearAndMonth] column is of Date type.

    Output = 
VAR previous_month = MAXX(FILTER(ALL('Table (5)'),'Table (5)'[YearAndMonth] < MAX('Table (5)'[YearAndMonth])),'Table (5)'[YearAndMonth])
VAR previous_month_sales = MAXX(FILTER(ALL('Table (5)'),'Table (5)'[YearAndMonth] = previous_month), 'Table (5)'[Sales])
RETURN
DIVIDE(SELECTEDVALUE('Table (5)'[AR]) - SELECTEDVALUE('Table (5)'[Sales]), previous_month_sales)

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

4 Replies

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

    Anonymous , for that you need a date and date table. If you do not have date

     

    Date = "01 " &[Yearand Month] //change data type to. Join with date of date table

     

    example measure

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

     

    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 :radacad sqlbi My Video Series Appreciate your Kudos.

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Anonymous 

    Try this measure to get the result. In my example, [YearAndMonth] column is of Date type.

    Output = 
VAR previous_month = MAXX(FILTER(ALL('Table (5)'),'Table (5)'[YearAndMonth] < MAX('Table (5)'[YearAndMonth])),'Table (5)'[YearAndMonth])
VAR previous_month_sales = MAXX(FILTER(ALL('Table (5)'),'Table (5)'[YearAndMonth] = previous_month), 'Table (5)'[Sales])
RETURN
DIVIDE(SELECTEDVALUE('Table (5)'[AR]) - SELECTEDVALUE('Table (5)'[Sales]), previous_month_sales)

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello,

    Create a measure,

    PreviousMonthSales = CALCULATE(SUM(TableName[Sales]),PREVIOUSMONTH(TableName[YearAndMonth]))
     
    then create another measure,
    Desired Result = DIVIDE(SUM(TableName[AR])-SUM(TableName[Sales]),[PreviousMonthSales])
    • ankurbajaj07's avatar
      ankurbajaj07
      Icon for Helper I rankHelper I
      3 years ago

      Anonymous amitchandak 

      I also faced same problem. I followed previous month formula in measure and it is providing the correct date , like reffering current month data to previous month date. But problem is that it is giving monthly data against each date and not against the month.

       

      Below I need to count rows in employee table "Roster Database" having category as Regular OR Fixed Term Hire. This i want to associate for previous month as I shall use this Measure for attrition rate.

      REGU EMP M = CALCULATE(COUNTROWS('Roster Database'),'Roster Database'[EMP.TYPE]="REGULAR"|| 'Roster Database'[EMP.TYPE]="FIXED TERM HIRES", PREVIOUSMONTH('Main Data table'[Date]))

       

      Pls help for unique date month.