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

 

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.