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

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

      • IF's avatar
        IF
        Post Prodigy
        6 years ago

        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