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