Forum Discussion

JulietZhu's avatar
JulietZhu
Icon for Helper IV rankHelper IV
8 years ago
Solved

Average payment for previous month

I need average payment for last month. For example, if I choose any date for  Feb, average payment for Jan need be calculated. Each day payment for Feb will be compared to overal average payment in J...
Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago

    I downloaded and looked at your file.  Only change I see to make is to remove the ".Date" part.  Try this:

     

    Avg_PreviousMonth_payment =
CALCULATE (
    AVERAGE ( DataFromDB[Payment_Amt] ),
    PREVIOUSMONTH ( CalendarTable[Date] )
)
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    JulietZhu

     

    One way to reduce is:

     

    Creating a calculated column in your calendar table

     

    DaysinMonth =
DATEDIFF (
    STARTOFMONTH ( CalendarTable[Date] );
    ENDOFMONTH ( CalendarTable[Date] );
    DAY
)
    + 1

    And in the measure:

     

    TotalPayment_PreviousMonth =
VAR DaysinPrevMonth =
    CALCULATE (
        SELECTEDVALUE ( CalendarTable[DaysinMonth] );
        PREVIOUSMONTH ( CalendarTable[Date] )
    )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( DataFromDB[Payment_Amt] );
            PREVIOUSMONTH ( CalendarTable[Date] )
        );
        DaysinPrevMonth
    )

    Regards

     

    Victor

    Lima - Peru

JulietZhu's avatar
JulietZhu
Icon for Helper IV rankHelper IV
8 years ago

Can someboday help me out? My project is stuck here for 2 days. I am new for power BI and noboday else can use power BI.

 

Please help. Thanks

  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    8 years ago

    I downloaded and looked at your file.  Only change I see to make is to remove the ".Date" part.  Try this:

     

    Avg_PreviousMonth_payment =
CALCULATE (
    AVERAGE ( DataFromDB[Payment_Amt] ),
    PREVIOUSMONTH ( CalendarTable[Date] )
)
    • JulietZhu's avatar
      JulietZhu
      Icon for Helper IV rankHelper IV
      8 years ago

      mattbrice, Thanks for your reply. It does work. Would you please explain what is difference with and without .Date? Thanks

       

      Also, even Avg calcualation is correct, but it is not what I want it.

       

      For example for BB , the payment total in Jan is -90353.54, but average from power BI is -140.30 becuase it is calculated based on total payment divide row number (644),  Avg from powe BI = -90353.54/644=-140.30 .

       

      But I want average for Jan , which need be divide day number, So Average should be = -90353.54/31 = -2914.63.   

       

      I do have 1 solution with 3 DAX.  I calcuated total Payment and distinct day for previous month. Then total/day number.

      TotalPayment_PreviousMonth = calculate(sum(DataFromDB[Payment_Amt]), PREVIOUSMONTH(CalendarTable[Date]))

      DistinctPreviousMonthDate = CALCULATE(DISTINCTCOUNT(CalendarTable[Date]),PREVIOUSMONTH(CalendarTable[Date]))

      Avg_PreviousMonth_payment = CALCULATE([TotalPayment_PreviousMonth]/[DistinctPreviousMonthDate])

       

      Since I will have so many those calcuations in my project and I tried to reduce numbers. It that way to write one DAX to calculate Avg_PrevousMonth instead of 3 DAX. Thanks

       

       

       

         

       

       

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        8 years ago

        JulietZhu

         

        One way to reduce is:

         

        Creating a calculated column in your calendar table

         

        DaysinMonth =
DATEDIFF (
    STARTOFMONTH ( CalendarTable[Date] );
    ENDOFMONTH ( CalendarTable[Date] );
    DAY
)
    + 1

        And in the measure:

         

        TotalPayment_PreviousMonth =
VAR DaysinPrevMonth =
    CALCULATE (
        SELECTEDVALUE ( CalendarTable[DaysinMonth] );
        PREVIOUSMONTH ( CalendarTable[Date] )
    )
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( DataFromDB[Payment_Amt] );
            PREVIOUSMONTH ( CalendarTable[Date] )
        );
        DaysinPrevMonth
    )

        Regards

         

        Victor

        Lima - Peru

    • SQUILES's avatar
      SQUILES
      Icon for Helper I rankHelper I
      7 years ago

      HI, I need to get the average of previous month to compare with the current month. I try to New Measure:

      Prev_Month_Ave =
            CALCULATE(AVERAGE('Query1'[DATO_46]),PREVIOUSMONTH(Query1[DATA_PERIODO]))
       
      But give me (BLANK) result.
       
      Any help please?
      My data look like: