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

     

     

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

 

 

  • Thomas_Paul28's avatar
    Thomas_Paul28
    Frequent Visitor
    7 years ago

    Thanks for your help Victor,

     

    I have a Calendar table and have created a relationship between calendar table date and month on my dataset. I slice using date on my calendar table, even setting it to a first of the month value e.g. 01 June 2019, however I get the following error:

     

    My measure is below where I have just substituted in the values from my data as suggested:

     

    My calendar table relationship looks like this:

     

    Many thanks

     

    Tom