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

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

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

    Vvelarde, Your guys are genius. Works Great.