Forum Discussion

IF's avatar
IF
Post Prodigy
6 years ago
Solved

previous value

Hi, I have to tables. Table "MonthYear" provides the months at the slicer. "Month" column is text. "Order" column is numeric. The other table is for calculation. I have a slicer for selection of mon...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi IF,

    Yes, it is possible.
    You can extract current date text value and use it to find out the index, then do the calculation to get the previous month index and use it as a condition to filter table records to find out the previous date text.

    BTW, I also modify measure formula to replace allselcted with all function, you can try it if it works:

    Previous result =
VAR _current =
    SELECTEDVALUE ( Actual[month], MAX ( Actual[month] ) )
VAR prev =
    FORMAT (
        DATE ( RIGHT ( _current, 4 ), LEFT ( _current, 2 ) - 1, 1 ),
        "mm.yyyy"
    )
RETURN
    CALCULATE (
        DIVIDE ( SUM ( Actual[Days] ), SUM ( Actual[req] ) ),
        FILTER ( ALL ( Actual ), [month] = prev )
    )

    Regards,

    Xiaoxin Sheng

ryan_mayu's avatar
ryan_mayu
Super User
6 years ago

IF 

 

I suggest you create a datetime table and create relationship between actual table and datetime table.

 

Since you need to connect two table by using date column, at first you change the month column to date type.

 

date = date(right(actual[month],4),left(actual[month],1),1)

 

Then create two measures

this month = sum(actual[Days])/sum(actual[req])

last month = CALCULATE(sum(actual[Days]),DATEADD('datetime'[Date],-1,month))/ CALCULATE(sum(actual[req]),DATEADD('datetime'[Date],-1,month))

 

Since you want to keep the filter as text, you need to switch the date column to text value in the datetime table

monthtext = month('datetime'[Date])&"."&year('datetime'[Date])

 

 

 

 

 

 

  • IF's avatar
    IF
    Post Prodigy
    6 years ago

    Hi!

    Thanks for the answer. Both tables are link to eachother through month column. The month colum in both tables are text. I don't want to change it into date format. As you said, maybe it is a better solution. However, I made some progress with my report. it will effect the rest. So, is there any possibility to have a measure while keeping the "month" column in both table in text format?

    All the best,

    IF