Forum Discussion
Helper IVAverage payment for previous month
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] ) )One way to reduce is:
Creating a calculated column in your calendar table
DaysinMonth = DATEDIFF ( STARTOFMONTH ( CalendarTable[Date] ); ENDOFMONTH ( CalendarTable[Date] ); DAY ) + 1And 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
Solution SageI 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] )
)
Helper IVmattbrice, 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
Community Champion
One way to reduce is:
Creating a calculated column in your calendar table
DaysinMonth = DATEDIFF ( STARTOFMONTH ( CalendarTable[Date] ); ENDOFMONTH ( CalendarTable[Date] ); DAY ) + 1And 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
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.