Parallelperiod -1, Month Showing current months sum instead of last month

[Skunny11]

Hey Everyone, so i have a measure for both total sum of a field and a measure for that same field except for the previous month. Yet in the Table visualization they come out to be the same number for the given month.

I have a date field in this table that is not contiguous (meaning records are added on the 1st of each month and given a date for that day), I have formatted this date field so that it shows the month and year so dec 18, jan 19, feb 19, etc...

Total = CALCULATE(SUM(mytable[numbertosum]))

Previous month = CALCULATE(SUM(mytable[numbertosum]), PARALLELPERIOD(mytable[date],-1,MONTH))

What i am seeing is the following:

Date      |  sum   |   previous month sum

nov 18   |  200    |             200

dec 18   |  800    |             800

Jan 19   |  300    |             300

Feb 19   |  400    |             400

When i am expecting to see:

Date      |  sum   |   previous month sum

nov 18   |  200    |               0

dec 18   |  800    |             200

Jan 19   |  300    |             800

Feb 19   |  400    |             300

I have tried PREVIOUSMONTH which doesnt return any sums for previous month, I have tried DATEADD which returns the same as parallelperiod.

Any help on this would be greatly appriciated.

[evandrocunico]

Hi @Skunny11 

try this https://community.powerbi.com/t5/Desktop/Same-period-last-month-not-working/td-p/335722

regards

[Anonymous]

You need to have a dedicated Date table, marked as a such, then related to your fact table. Then use the columns from that table in your measures

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

[Skunny11]

I cant create a separate date table because the dates are not contiguous for that field in the other table. 

I get the Error: The date column must have unique values. The date column can't have gaps in dates.

Also I have already tried to use DATEADD and it does the same thing as parallelperiod

[Anonymous]

Hi,

Create a date table using the below formula and create a relationship it with your existing table:

Table:

Calender = CALENDAR(MIN('TableName'[ColumnName]), MAX('TableName'[ColumnName]))

Add the following columns to the Calender table:

1) FY = if(MONTH(Calander[Date])<=3,YEAR(Calander[Date]),YEAR(Calander[Date]))

2) Month = FORMAT(Calander[Date], "mmmm")

Once you create this table with above mentioned the columns and create a relationship between both the tables, you'd be able to get the result you're looking.

Regards,

Mahesh

[Skunny11]

I created the new table as you listed, created a relationship from the date field in my first table and the calendar date field. This does not fix my problem, i am still seeing the same behavior, only now the same behavior is being displayed while using PREVIOUSMONTH as well as DATEADD and PARALLELPERIOD. All so the same data as the current month in the previous month column.

[Anonymous]

@Skunny11 

Can you upload a sample pbix?

[Skunny11 · Accepted solution]

I figured out what was happening.

It had to do with the way I was filtering other fields in the measure. I didnt include those filters because I didnt think they were relavant to the problem.

My actual measure used to be this:

Measure_Previous_Month = 
CALCULATE
( 
    SUM(mytable[myitemtocount]),
    FILTER
    ( mytable,
        PARALLELPERIOD(mytable[Date],-1,MONTH) && (mytable[edition] = "myedition") && (mytable[type] = "mytype" || mytable[type] = "mytype2")
    ) 
)

When i got rid of that nested filter and just did it the regular way it ended up working as intended, not sure i understand the logic on why.

Measure_Previous_Month = 
CALCULATE
( 
    SUM(mytable[myitemtocount]), 
    PARALLELPERIOD(mytable[Date],-1,MONTH),
    (mytable[edition] = "myedition"), 
    (mytable[type] = "mytype" || mytable[type] = "mytype2")
)