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

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's avatar
JulietZhu
Icon for Helper IV rankHelper IV
8 years ago

mattbrice, thank for information. Learned another new function.

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

    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's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    8 years ago

     

    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's avatar
    JulietZhu
    Icon for Helper IV rankHelper IV
    8 years ago

    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's avatar
    JulietZhu
    Icon for Helper IV rankHelper IV
    8 years ago

    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.