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

MonthOrder
06.20204
05.20203
04.20202
03.20201

 

Actual

Daysreqmonth
5406.2020
4206.2020
3206.2020
5206.2020
3305.2020
5205.2020
7505.2020
5505.2020

Thanks in advance!

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

6 Replies

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      IF
      Post Prodigy
      6 years ago

      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:

      • Anonymous's avatar
        Anonymous
        Not applicable
        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