Conditional Formatting Based on Previous Period

[Testsubjec]

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.

[Anonymous · Accepted solution]

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

[amitchandak]

@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