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

     

     

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