PREVIOUSMONTH for text values

[Thomas_Paul28]

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

[dax]

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]

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.

Element	Prior Month Amount	Prior Month Currency (Measure 4)	Current Month Amount	Current Month Currency
Net Salary	3000	USD	3000	USD
Transport	100	USD	100	USD
Expenses	1100	GBP	300	USD

To be clearer my data sample looks like the below:

Month	ID	Element	Amount	Currency
01/07/2019	14650	Net Salary	3000	USD
01/07/2019	14650	Transport	100	USD
01/07/2019	14650	Expenses	1100	GBP
01/08/2019	14650	Net Salary	3000	USD
01/08/2019	14650	Transport	100	USD
01/08/2019	14650	Expenses	300	USD

Many thanks!

Tom

[dax]

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]

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 · Accepted solution]

@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]

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