Forum Discussion

Thomas_Paul28's avatar
Thomas_Paul28
Frequent Visitor
7 years ago
Solved

PREVIOUSMONTH for text values

Hi,

 

New to PowerBI. Is there any way to use PREVIOUSMONTH for text values? I have a matrix, the rows are different pay elements, the columns are current month amount, current month currency, previous months amount. I want to add previous months currency but CALCULATE and PREVIOUSMONTH doesn't work (this is what i've used to get previous months amount). Feel like it should be simple, like replacing calculate for something else because it's text but i can't find a solution anywhere!

 

Many thanks in advance

Need Help
  • Vvelarde's avatar
    Vvelarde
    7 years ago

    Thomas_Paul28 

     

    Hi, Thomas:

     

    1. I reccomend it always use a calendar table to easy work with dates. You can create a simple one with CalendarAuto Function. Related it with your dataTable. You also can Add a New Columns with the Years, Quarters, Months, etc-

     

    2. Works with measures in every that you can with agreggations. (SUM, MIN, MAX, etc)

     

    3. Now for your question, one way to solve it is :

     

    CurrentMonthAmount = SUM(Table1[Amount])
    CurrentMonthCurrency = SELECTEDVALUE(Table1[Currency],BLANK())
    Previous Month Amount = 
Var _MINDATE=EDATE(MIN(CalendarTable[Date]), -1)
RETURN
IF(HASONEVALUE(Table1[Element]),CALCULATE(SUM(Table1[Amount]),FILTER(ALL(CalendarTable),CalendarTable[Date]=_MINDATE)))
    Previous Month Currency = 
Var _MINDATE=EDATE(MIN(CalendarTable[Date]), -1)
RETURN
IF(HASONEVALUE(Table1[Element]),CALCULATE(VALUES(Table1[Currency]),FILTER(ALL(CalendarTable),CalendarTable[Date]=_MINDATE)))

    Use in the slicer the month column of your calendar table.

     

    Regards

     

    Victor

     

     

6 Replies

  • dax's avatar
    dax
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Thomas_Paul28,

    Below is my design(I use simple sample, if your sample is not similar to mine, please inform me your data sample)

    id ym amount month
    1 2019 12 1
    1 2019 10 2
    1 2019 3 3
    1 2019 5 4
    2 2019 20 1
    2 2019 15 2
    2 2019 3 3
    2 2019 20 4
    3 2019 13 1
    3 2019 3 2
    3 2019 25 3
    3 2019 20 4

    then I create two measures

    temp =
CALCULATE (
    SUM ( test[amount] ),
    FILTER ( ALLEXCEPT ( test, test[id] ), test[month] = MIN ( test[month] ) - 1 )
)


Measure 4 = IF(HASONEVALUE(test[month]),[temp], SUMX(test,[temp]))

    Then you could create matrix like below

    Best Regards,
    Zoe Zhi

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Thomas_Paul28's avatar
      Thomas_Paul28
      Frequent Visitor
      7 years ago

      Hi Zoe,

       

      Thankyou for the quick response! :) The below is how my matrix looks and 'prior month currency' is what im trying to get to populate. Unfortunately your solution seems to be returning that column as blank.

       

      ElementPrior Month AmountPrior Month Currency (Measure 4)Current Month AmountCurrent Month Currency
      Net Salary3000USD3000USD
      Transport100USD100USD
      Expenses1100GBP300USD

       

       

      To be clearer my data sample looks like the below:

       

      MonthIDElementAmountCurrency
      01/07/201914650Net Salary3000USD
      01/07/201914650Transport100USD
      01/07/201914650Expenses1100GBP
      01/08/201914650Net Salary3000USD
      01/08/201914650Transport100USD
      01/08/201914650Expenses300USD

       

      Many thanks!

       

      Tom

      • dax's avatar
        dax
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi

        Based on your sample, you could try to create measures like below  and use Table to show it

        current = CALCULATE(SUM(tt[Amount]),FILTER(tt, YEAR(tt[Month])=YEAR(TODAY()) && MONTH(tt[Month])=MONTH(today())))

current c = CALCULATE(MIN(tt[Currency]),FILTER(tt, YEAR(tt[Month])=YEAR(TODAY()) && MONTH(tt[Month])=MONTH(today())))
previous = CALCULATE(SUM(tt[Amount]), FILTER(tt, YEAR(tt[Month])=YEAR(TODAY()) && MONTH(tt[Month])=MONTH(today())-1))
previous c = CALCULATE(MIN(tt[Currency]), FILTER(tt, YEAR(tt[Month])=YEAR(TODAY()) && MONTH(tt[Month])=MONTH(today())-1))

        Best Regards,
        Zoe Zhi

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.