How to extract previous month sales as a grouped column

[Ancella]

I'm unable to create a calculated table where I'm trying to group the following margin% by cities and arrive at 2 columns: current month margin% and previous month margin%. I have tried several formulae: previousmonth, dateadd and it does not seem to work.

please help!!

dataset:

dax used:

desired outcome:

[rajendraongole1]

Hi @Ancella - can you try the below calculated table and rename the source tables at your end.

CityMarginSummary =

SUMMARIZE(

    'Calct',

    'Calct'[Beg_of Month],

    'Calct'[City],

    "Current Month Margin%",

        CALCULATE(

            AVERAGE('Calct'[Margin])

        ),

    "Previous Month Margin%",

        CALCULATE(

            AVERAGE('Calct'[Margin]),

            FILTER(

                ALL('Calct'),

                'Calct'[City] = EARLIER('Calct'[City]) &&

                'Calct'[Beg_of Month] = EOMONTH(EARLIER('Calct'[Beg_of Month]), -1)

            )

        )

)

Hope it works, still any please share some sample data will check on this.

[Ancella]

Hi @rajendraongole1 ,

Thank you for the solution. But as per your table Previous month margin% seems to be empty.

Upon applying it to my dataset also I get an empty column. Can you please help with a column that would populate the column:

refer "Margin last month"

[Greg_Deckler]

@rajendraongole1 Can you share the sample data you posted as text?

[Ancella]

Hi @Greg_Deckler , 

thank you for your response.. I can share my sample dataset, if this works. 

Please note: Margin% column is a calculated measure.. while I'm trying to create a calculated table with current month and previous month margins, none of the time intelligence functions work.. they're either showing empty results or same values as current month

Ancella_dataset.xlsx

[Ashish_Mathur]

Hi,

Why do you want to create a calculated table?  Why not a measure?

[Greg_Deckler · Accepted solution]

@Ancella OK, see attached PBIX file below signature:

Current Month = 
    VAR __MaxMonth = MONTH( MAX( 'Table'[Beg_of_month] ) )
    VAR __Result = AVERAGEX( FILTER( 'Table', MONTH( [Beg_of_month] ) = __MaxMonth ), [Margin %] )
RETURN
    __Result

Last Month = 
    VAR __MaxMonth = MONTH( MAX( 'Table'[Beg_of_month] ) )
    VAR __Result = AVERAGEX( FILTER( 'Table', MONTH( [Beg_of_month] ) = __MaxMonth - 1 ), [Margin %] )
RETURN
    __Result

[Greg_Deckler]

@Ancella OK, I'll take a look. In the mean time, you may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...