cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SHDJason
Advocate II
Advocate II

parallelperiod vs previousmonth vs adddate

Hi All,

 

I found a solution to this already but am confused about why it is so.  

 

I need to find a simple previous month lookup.  

 

Prev. Month Sales = CALCULATE(SUM([Total Sales]),DATEADD([Date]),-1,MONTH))

 

Prev. Month Sales = CALCULATE(SUM([Total Sales]),PREVIOUSMONTH([Date]))

 

Prev. Month Sales = CALCULATE(SUM([Total Sales]),PARALLELPERIOD([Date],-1,MONTH))

 

The first 2 measures return null. The last one gives me the right answer. Why? Aren't these all the same?  

 

Thanks for your thoughts.

1 ACCEPTED SOLUTION

Hi @SHDJason,

 

In DAX, PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. So please check if the dates in date column are continuous. If the dates in the current context do not form a contiguous interval, the function returns an error.

 

Reference:
PARALLELPERIOD Function (DAX)

 

Regards,

View solution in original post

14 REPLIES 14
SamiTi
Frequent Visitor

Hi 

 

All measures above are working well.

 

However am getting BLANK when i try to view this by stores/product/category.

 

Measure --> PPREVMONTH = CALCULATE([Volume],PREVIOUSMONTH('dim MS Cal'[Date]))

 

Thanks for your help or advise.

 

CaptureTable.JPG

 

Thanks

Sami

Hi @SamiTi,

I have the same problem did you find a solution?

BR,
Noa.
SamiTi
Frequent Visitor

Hi, The solution is that you need to select 1 month in order for measure to return Previous month value. 

 

Hope this helps,

Sam

Anonymous
Not applicable

Hi,

 

I have the same problem. I try to show sales by customers but PREVIOUSMONTH function gives me a blank column.

 

The PARALLELPERIOD function gives me a sum of the sales for last month AND last year last month. (August 2017 + August 2018).

 

The DATEADD function gives me the same result as the PARALLELPERIOD function.

 

These results are obviously not what I expect.

 

(Yes I do have a date table with a relationship between my date table and my sales table.)

 

Could someone help ?

 

Thank you

 

 

SamiTi
Frequent Visitor

Hi,

 

Test on new page.

 

Create and Select from dropdown. August 2018

 

Create Table chart:

-Customer 

-Sales measure.

-Sales Previous Month measure = July 2018.

 

Let me know if this helps

 

Thanks

Sam

Anonymous
Not applicable

Hi SamiTi,

 

Thanks for replying. I don't really understand what you suggest me to try though, sorry.

Here's a screenshot of what my report looks like until now.

 

I don't want to have to apply a filter myself because my intention is to publish this report in the Power BI service and other users will daily consult it. So it has to calculate last month sales itself without my intervention.

 

Thanks again

Ben

Example.png

SamiTi
Frequent Visitor

Hi Ben

 

Add a slicer with  month year in order for measure to return Previous month value. 

 

User will need to select Month from Slicer.

 

parallelperiod.JPG

Anonymous
Not applicable

It works !!!!!!!!

 

I have been traveling on blogs since 3 days now...

 

Thank you so much !!!!

Hi @SamiTi

I figured it our:

 

Parallel Period = 
VAR MinDate = MIN ( Date[Column] )
VAR StartDate = DATE ( YEAR ( MinDate ) - 1, MONTH ( MinDate ), DAY ( MinDate ) )
VAR MaxDate = MAX ( Date[Column] )
VAR EndDate = DATE ( YEAR ( MaxDate ) - 1, MONTH ( MaxDate ), DAY ( MaxDate ) )
RETURN
CALCULATE ( 
	[Measure],
	FILTER ( 
		ALL ( DateColumn/Table ),
		Date[Column] >= StartDate
			&& Date[Column] <= EndDate
	)
)
BR,
Noa.
Sean
Community Champion
Community Champion

@SHDJason

All 3 should be working. Do you have a Calendar Table though?

 

EDIT: Use Matt's link to create if you don't have - explains step by step - how to create

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

 

@Sean

 

Yes i do have a calendar table. I tried using that and the date column in the data table. Both worked, but only for parallelperiod.  

 

Is it possible that PowerBI caches the data and then does not always refresh? Sometimes, if I create a formula incorrectly then correct it, the new calculated values do not load. But if I refresh the dataset, they do load. I'm wondering if it is a cache issue. 

Hi @SHDJason,

 

In DAX, PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. So please check if the dates in date column are continuous. If the dates in the current context do not form a contiguous interval, the function returns an error.

 

Reference:
PARALLELPERIOD Function (DAX)

 

Regards,

Hi, 

 

Above measure and functions are all working well as mentioned.

 

However i cant see the Previousmonth sales BY category/store/product division all i get is "blank".

 

 

screenshot Screenshot

Sean
Community Champion
Community Champion

@SHDJason

This is what I get with the Measures - numbered in the order you listed them (top to bottom)

 

If you don't have a Month field though they'll either total OR

as is the case with the 2nd Measure it will be blank because it doesn't have any reference month

 

PM Calculations.png

 

If you change a Measure it should immediately reflect that change - but that doesn't trigger data refresh.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors