Average payment for previous month

[JulietZhu]

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

[mattbrice · Accepted solution]

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]

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

[JulietZhu]

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 · Accepted solution]

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

[SQUILES]

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:

My GoogleDrive Data

[JulietZhu]

@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

[mattbrice]

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]

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]

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

[JulietZhu]

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

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

[mattbrice]

Does your date slicer include the first date of the month?  Date manipulation functions work by sliding the visible window of dates forwards or backwards depending on options.  If slicer date range is 2/3/2017 ... 2/25/2017  then DATEADD(CalendarTable[Date], -1, month)  returns 1/3/2017 ... 1/25/2017.  Then FIRSTDATE (DATEADD(CalendarTable[Date], -1, month)) will return 1/3/2017.

[JulietZhu]

@mattbrice You are right, my slice from 2nd day. Not first day. I thought firstdate is the 1st calendar day of each month. Actually it is the first date of slicer range. For my case, obviously I need 1st calendar day of each month and that is why EOMONTH(MIN()) works. Thanks so much for explantion. Total get it. Thanks soooooo much.

[mattbrice]

Great.  

And FYI you could also do:  STARTOFMONTH ( DATEADD ( Calendartable[Date] , -1, month ) )

and you will get the first day of the month based on the first date visible in the current filter context and then not worry about having to be as careful about slicer dates.

[JulietZhu]

@mattbrice, thank for information. Learned another new function.

[JulietZhu]

4 average measures have been calculated so far including Last 30 days, Last 60 days, Previous month, Previous 2 months. Now I need drop down list and let user to choose which average you want to see. My idea is adding a slicer table with Average Option column which includes the above 4 average name. In the Measure table add four measures and DAX samples are below. 

Avg_Payment_Last30DaysSlicer = IF(
                                   CONTAINS(AverageSlicerTable,AverageSlicerTable[AverageOption],"Last 30 days"),
                                   MeasureFromSqlServer[Avg_PaymentAmt_Last30Days],
                                   BLANK()
                                   )

                                   )

But after I add all the slicer measures to the value and choose the option from slicer, all measures’ header are shown below include blank measures (left table). I am looking for  the way only shows the measure header and number that you chose? For example, If I choose last 60 days, only this measure shows in the table (like right table). Other blank measures including header won't show. Thanks

 

[mattbrice]

Set your card to use only the following measure:

Slicer_measure =
VAR _slicerSelection =
    SELECTEDVALUE ( slicer_Table[measure], "Avg_payment_Last_30days" )
RETURN
    SWITCH (
        _slicerSelection,
        "Avg_payment_Last_30days", [Avg_payment_Last_30days],
        "Avg_payment_Last_60days", [Avg_payment_Last_60days],
        "Avg_payment_PreviousMonth", [Avg_payment_PreviousMonth],
        "Avg_payment_Previous2Month", [Avg_payment_Previous2Month]
    )

But unfortunately the card will only show the category label "Slicer_Measure" and there are no formatting options to programatically change category label afaik.  But what you can do is add another card with this measure :

slicer_Measure text = SELECTEDVALUE ( slicer_Table[measure], "Avg_payment_Last_30days" )

[JulietZhu]

@mattbrice, Switch is enough since it only shows the measure chosen. That is exactly what I am looking for.

 For text box, it is no problem since I will put the slicer aside the measure and user will know which average measure they choose.

Thanks so much again for your quick reply.

[JulietZhu]

Further requirement for this project. Need layout like below

But in power bi  those 9 numbers are from 9 different measures. The layout I got so far frow power BI is below.

All the texts are from text boxes and numbers are from multirow card without catagory lable.

          

But in this way, I can't adjust the distance bewteen row and columns and can't add gridline on columns or rows.  What is best way to do it? Thanks for help.                                                                                                                                                                                                                       

[Vvelarde · Accepted solution]

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

@Vvelarde, Your guys are genius. Works Great.