Calculate volume next month

[Anonymous]

Hi guys,

I've the following table columns: VOLUME, PEOPLE and EOMONTH. I need to calculate the volume of the next month like the example bellow:

VOLUME	PEOPLE	EOMONTH	Calc_Column
43	ABC	31/03/2021
14	DEF	31/03/2021
5	GHI	31/03/2021
7	JKL	31/03/2021
44	ABC	28/02/2021	43
16	DEF	28/02/2021	14
5	GHI	28/02/2021	5
7	JKL	28/02/2021	7

Can any one help me?

Tks

[v-yangliu-msft]

Hi  @Anonymous ,

Here are the steps you can follow：=

1. Create calculated column.

Month = MONTH('Table'[Eomonth])

Calc_Column =
CALCULATE(SUM('Table'[Volume]),FILTER('Table','Table'[Month]=EARLIER('Table'[Month])+1&&'Table'[People]=EARLIER('Table'[People])))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous Anyhow @mahoneypat provided the solution but why would you don't want to use the date table? Why not follow the best practice rather doing un-necessary DAX measures. Just trying to understand the thought process here.

[mahoneypat · Accepted solution]

Here is a column expression with one way to do it.  Replace Volume with your actual table name.

NextMonth =
VAR vNextDate =
    EOMONTH (
        Volume[EOMONTH],
        1
    )
RETURN
    CALCULATE (
        SUM ( Volume[VOLUME] ),
        ALLEXCEPT (
            Volume,
            Volume[PEOPLE]
        ),
        Volume[EOMONTH] = vNextDate
    )

Pat

[parry2k]

@Anonymous you can easily achieve this using the DAX time intelligence function called NEXTMONTH, You need a date table in your model to make it work, and that you can easily add it by looking at my blog post here.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi parry2k, I know how to create a new date table to do this, but my ask was about to do this in the same table. Ty