Forum Discussion

ScarlettBebb's avatar
ScarlettBebb
Frequent Visitor
3 years ago
Solved

Previous Month showing blank (percent difference calculation)

Hello all,

 

I was wondering if anyone could help with this error? I have a table as follows

 

 

From this I created a series of measures like this 

 

Average FPA = AVERAGE('Product FPA'[FPA])
Prior Month FPA = CALCULATE([Average FPA], PREVIOUSMONTH('Product FPA'[Attribute].[Date]))
% Change = DIVIDE([Average FPA], [Prior Month FPA], blank())-1

 

 

Then I wanted to make a table visual to show me the %change of FPA month to month for each product. But the previous month shows as Blank (and I've tried changing data hierchy). See visual below. I think the % change doesn't work yet because of the previous month error. I don't know if this makes a difference but my Date is the 01st of every month from 2016 to 2022. 

 

 

Any help would be really appreciated 

 

Thank you 

 

Scarlett

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    If you do not have DIM-calendar table, one of ways to achieve this is to create a measure like below.

    I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

    Prior month value avg: =
CALCULATE (
    [Value avg:],
    'Table'[Attribute]
        = MAXX (
            FILTER (
                ALLSELECTED ( 'Table'[Attribute] ),
                'Table'[Attribute] < MAX ( 'Table'[Attribute] )
            ),
            'Table'[Attribute]
        )
)

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    If you do not have DIM-calendar table, one of ways to achieve this is to create a measure like below.

    I tried to create a sample pbix file like below, and I hope the below can provide some ideas on how to create a solution for your datamodel.

     

     

     

    Prior month value avg: =
CALCULATE (
    [Value avg:],
    'Table'[Attribute]
        = MAXX (
            FILTER (
                ALLSELECTED ( 'Table'[Attribute] ),
                'Table'[Attribute] < MAX ( 'Table'[Attribute] )
            ),
            'Table'[Attribute]
        )
)