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

     

     

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.

    • Thomas_Paul28's avatar
      Thomas_Paul28
      Frequent Visitor
      7 years ago

      Hi Zoe,

       

      This dosn't seem to work for me :(  I used your measures and subtituted in the correct elements from my data but they return blank. Regardless I have an additional criteria which i neglected to mention because I was focussed on finding a way to use PREVIOUSMONTH...  

       

      I have a slicer for month selection. Therefore I'd like to be able to select a month, say May 2019 and the Previous Currency column then show currency for April 2019 and then select April 2019 and see currency for March 2019 which I don't believe would work using the table and filters.

       

      It's so simple for previousmonth amount! if only it were the same for currency.

       

      Again thanks so much for all your help, hugely appreciated.

       

      Tom

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        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