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

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

Or you can do this: (with no need for extra column in calendar table ):

Avg_PreviousMonth_payment =
VAR _previousMonth = PREVIOUSMONTH ( CalendarTable[Date] )
VAR _countDaysPreviousMonth = COUNTROWS ( _previousMonth )
VAR _sumPaymentAmt = CALCULATE ( SUM ( DataFromDB[Payment_Amt] ), _previousMonth )
RETURN
    DIVIDE ( _sumPaymentAmt, _countDaysPreviousMonth )

"...what is difference with and without .Date? "

 

Power BI Desktop in its infinite wisdom tries to help unwitting users by automatically adding hidden "Calendar" tables to data model for every Date column it finds.  These extra tables are accessed using the dot notation ".[Date]".  Your problem was that PREVIOUSMONTH uses the first date visible in the current filter context which for the hidden .[Date] calendar is probably the first date in CalendarTable since your slicer put a filter on "CalendarTable[Date]" and not "CalendarTable[Date].[Date]" (Filters are column specific).  It then tries to go back a month from this first date (which doesn't exist) so it returns BLANK().

 

My personal preference is to turn off this feature and add my own Calendar tables when/where needed.  It can be turned of in Options and Settings -> Options -> Data Load -> Auto Date / Time.  But it is a personal choice.

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

I also need average for previous 2 monthes . I want to calculate total and days to get the result correct first .

 

This is error messae when I  calculate total for 2 prvevious monthes 

 

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

    Change 'EOMONTH' function to 'ENDOFMONTH' function and should work.

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

      mattbrice Thanks for quick reply. Yes. Syntax works and also learned ENDOFMONTH.

       

      But the data is not correct. I need calculate the sum between 12/1/2017 to 1/31/2018. The total should be -180857.3.  But with this formula in power bi is  -177120.32. The difference is -3736.98, which is the payment from the beginning date 12/01/2017.

      Does this mean datesbetween in power bi is not include the start date point? We know between in sql server includes both start and end date point.

       

       

      BTW, if EMONTH() changed into EOMONTH(max(CalendarTable[Date]),-1))), it also works.

       

       

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

        Solved. Start date is changed from first day into EOMONTH(min()). Datesbwtween in power bi will inculde both start and end date.

         

        TotalPayment_Previous2Monthes = calculate(sum(DataFromDB[Payment_Amt]), DATESBETWEEN(CalendarTable[Date],EOMONTH(MIN(CalendarTable[Date]),-3)+1,EOMONTH(max(CalendarTable[Date]),-1)))

         

         

        But not sure why the start date will be excluded when I use FIRSTDATE(DATEADD(CalendarTable[Date],-2,month)). Somebody can explain why. Thanks