Forum Discussion

Testsubjec's avatar
Testsubjec
Regular Visitor
4 years ago
Solved

Conditional Formatting Based on Previous Period

I'm trying to add conditional formatting based upon the previous peroid via for each Depot ( please see below ).

 

 

I tried the below, which seems to bring back the same grand revenue for each period.

 

Test = 
VAR _val =
    CALCULATE (
        SUM(Finance[GrandRevenue])
    )
        - CALCULATE (
          sum(Finance[GrandRevenue]),
            'Calendar'[SagePeriod] = -1 && 'Calendar'[SageYearOffset] = 0 
        )
RETURN
    SWITCH (
        TRUE (),
        _val < 0, -1,
        _val = 0, 0,
        _val > 0, 1
    )

 

 

Any ideas??

 

Thanks.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Testsubjec , Period should be part of separate table having period , year period or year period rank

     

    example

     


    Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)

     


    This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
    Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))

     

     

    you can use period in place of period rank

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Testsubjec ,

    I created a sample pbix file(see attachment) for you, please check whether that is what you want.

    1. Create a measure as below:

    Measure = 
VAR _curperiod =
    SELECTEDVALUE ( 'Finance'[Period] )
VAR _curdepot =
    SELECTEDVALUE ( 'Finance'[Depot] )
VAR _preperiod =
    CALCULATE (
        MAX ( 'Finance'[Period] ),
        FILTER (
            ALLSELECTED ( 'Finance' ),
            'Finance'[Depot] = _curdepot
                && 'Finance'[Period] < _curperiod
        )
    )
VAR _prerevenue =
    CALCULATE (
        SUM ( 'Finance'[GrandRevenue] ),
        FILTER (
            ALLSELECTED ( 'Finance' ),
            'Finance'[Depot] = _curdepot
                && 'Finance'[Period] = _preperiod
        )
    )
RETURN
    IF (
        ISBLANK ( _prerevenue ),
        0,
        IF ( SUM ( 'Finance'[GrandRevenue] ) - _prerevenue > 0, 1, -1 )
    )

    2. Use conditional formatting on Values field 

    In addition, you can refer the following links to get it.

    How to Compare the Current Row to the Previous Row Using DAX

    How to Compare the Current Row to the Previous Row by Category Using DAX

    Use conditional formatting in tables

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards