Home Forums Power Pivot NextMonth is returning same month next year?

This topic contains 3 replies, has 2 voices, and was last updated by  m3tr01d 2 years, 2 months ago.

Viewing 4 posts - 1 through 4 (of 4 total)
  • Author
    Posts
  • #7800

    ehans
    Participant
    • Started: 5
    • Replies: 9
    • Total: 14

    I am totally banging my head against the wall. I wanted to play around with some date math in PPVt so started with NextMonth(). I kept getting blanks, so figured maybe I was doing something wrong so switched to the DateAdd() function. Same thing, blanks!

    Here are my formulas:

    Total Sales:=SUMX(tblModelSales,tblModelSales[Price]*tblModelSales[Qty])
    Total Sales Next Month:=CALCULATE([Total Sales],NEXTMONTH(Dates[Date]))
    Next Mo Take 2:=if(HASONEVALUE(Dates[Year]),
    CALCULATE([Total Sales],DATEADD(Dates[Date],1,MONTH))
    ,BLANK())

    Total Sales works fine. But the other two formulas return banks.

    So I double clicked on the February blank cell in my table and it showed that Feb 1-Feb 29, 2016 were the original formula, then it showed Feb 1-Feb 28, 2017 in the 2nd part. Well of course those are blank – my sample data is just calendar 2016.

    Doesn’t next month do next month? Meaning if my row is on Feb, shouldn’t the NEXTMONTH() function give me March data of the same year?

    I do have a date table with all relevant dates. I am attaching the file. It is just play data. My date table is coming from PowerQuery.

    What am I doing wrong? This seems like it should be so simple.

    Attachments:
    You must be logged in to view attached files.
    #7804

    m3tr01d
    Participant
    • Started: 0
    • Replies: 17
    • Total: 17

    Hi ehans,

    I’m no expert in Powerpivot but I think you’re not using NextMonth() at the right level of aggregation. Im pretty sure the reason you’re having blank is because you are using NextMonth inside a measure and the month field is in the row label section.

    That means Powerpivot is trying to find the next month but he seems to be already filtered on a specific month.

    Did you try using a calculated column to calculate your nextmonth?

    Hope it helps.

    #7806

    ehans
    Participant
    • Started: 5
    • Replies: 9
    • Total: 14

    The date functions for NEXT/PREVIOUS are meant to be used in a measure. I may not be using it right, but I think I am using it in the right place.

    #7807

    m3tr01d
    Participant
    • Started: 0
    • Replies: 17
    • Total: 17

    Hi ehans,

    yeah, my mistake, you’re right about that :P.

    Hmm, I tried something with your file example and when I’m displaying the date field on the rows, the total sales for next month seems to work.

    I’m not so sure what the problem is. I’ll keep looking into it…

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.