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 Jan.

 

I use the following DAX, but it gives me blank value

Avg_PreviousMonth_payment = CALCULATE(AVERAGE(DataFromDB[Payment_Amt]),PREVIOUSMONTH(CalendarTable[Date].[Date]))

 

 

Sample pbix file is in the link. Please help. Thanks

https://1drv.ms/u/s!AlYpYKwSuOKxhDxAw_liCgmuWm98

 

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

23 Replies

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

    How to attach pbix dummy data here. I still stuck in this calculation. Need help

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

      JulietZhu.

       

      Hi, Upload to Drive, OneDrive,DropBox or another similar and post the link

       

      Regards

      Victor

  • 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

         

         

         

           

         

         

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

    mattbrice, Thanks for explanation.

     

    Just notice you registered the same day of today last year. Happy one year registration annivesary. And happy Valentine's day.