Sales Sum of next month

[ce2016]

Dear all,

I am looking for a way to sum demand of the next month (rolling).

Regarding the data set I do only have monthly data that is displayed with the first day of the month (01.MM.YYYY)

I did try an approach with the NEXTMONTH function:

Sum Demand next month = CALCULATE (SUM('Input'[Total Demand Quantity]); NEXTMONTH('Stock Cover_Input'[Date]))

However this did not work.

So overall it should like this, but in the column "Sum demand next month" the sum of demand of the next month should be shown.

Do you have any idea how to make this work?

Thank you very much in advance!

[Zubair_Muhammad]

@ce2016

Give this a shot please

Sum Demand next month =
VAR mydate =
    EOMONTH ( SELECTEDVALUE ( 'Stock Cover_Input'[Date] ), 0 ) + 1
RETURN
    CALCULATE (
        SUM ( 'Input'[Total Demand Quantity] ),
        'Stock Cover_Input'[Date] = mydate
    )

[ce2016]

Hey @Zubair_Muhammad, it did not work either unfortunately...only a blank column as a result 😕

[Interkoubess]

Hi @ce2016,

Please create a calendar table (with continuous dates that cover your whole period) and then link it to your data model.

Then use your formula, it should work.

I made a test and it worked on my side.

Ninter. 

[ce2016]

Hi @Interkoubess,

I created a Date table, following the instructions of this webpage:

https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-step...

Then I linked the Date table with my main data table via date as key (1:*-relationship).

However, this also does not seem to work..

Data rows are always dated on the first of a month, does it have to do something with that eventually?

Thanks!

[Interkoubess]

Hi @ce2016,

Please share dummy data and I will help.

Thank you.

Ninter

[ce2016]

Hi Ninter @Interkoubess,

Thank you so much for your help.

I uploaded a dummy file on a dropbox folder:

https://www.dropbox.com/s/zcxlult57kwgfle/Dummy%20Sum%20Demand%20Nxt%20Month.pbix?dl=0

Thanks again for your support!

[ChandeepChhabra · Accepted solution]

Hi,

Try this

1. Setup a relationship between Dummy[Date] and Date[Date]. Currently the relationship was setup incorrectly

2. Edit your [Sum Demand Next Month] measure and use the following code = CALCULATE(SUM(Dummy[Total Demand]),DATEADD('Date'[Date],1,MONTH)) 

3. Use Dates in the table rather than cal year/ month

Now the table should work. A snapshot

Take a look at the pbix attached

[ce2016]

Works! Thank you guys!