previous value

[IF]

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 month and I have two cards. If I select "06.2020" I want to get result in two cards one for "06.2020" and the other card should show data for "05.2020". The second card should always show order-1 data. For the first card, I get the result "DIVIDE(SUM(Actual[Days]), SUM(Actual[req]))". I don't how to get the result for second card. Also I want to keep the "month" with text format.

MonthYear

Month	Order
06.2020	4
05.2020	3
04.2020	2
03.2020	1

Actual

Days	req	month
5	4	06.2020
4	2	06.2020
3	2	06.2020
5	2	06.2020
3	3	05.2020
5	2	05.2020
7	5	05.2020
5	5	05.2020

Thanks in advance!

[v-shex-msft]

HI @IF,

If you not want to convert your fields as date formula, you may need some conversion variable to do transform between these value and calculate the previous/next value and use it as filter conditions.

Measure formulas:

current result =
DIVIDE ( SUM ( Actual[Days] ), SUM ( Actual[req] ) )

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

Regards,
Xiaoxin Sheng

[IF]

Hi,

Thanks for the reply. I tried it didn't work. I want to highlight that the "month" column in both tables are in text format. I would like to use the " order" column in order to get previous month. Would it be possible?

Here is the image that I took:

[v-shex-msft · Accepted solution]

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

[IF]

Hi,

Thanks a lot! The measure works. You said that I can extract the current date text value and use it to find out index. For example; if a user selects "05.2020" the order (I mean index) for it is 3. How can I extract it?

On the other hand, previous month will be 3-1, which is 2. I want to show value for 2, which corresponds the previous month. How can I do it? You already mentioned but I didn't really understand it.

Bwy, I will accept the solution, but this way can be easier to use.

Best regards,

IF

[ryan_mayu]

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

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