parallelperiod vs previousmonth vs adddate

[SHDJason]

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.

[SamiTi]

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.

Thanks

Sami

[Noak]

Hi @SamiTi,

I have the same problem did you find a solution?

[SamiTi]

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]

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]

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]

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

[SamiTi]

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.

[Anonymous]

It works !!!!!!!!

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

Thank you so much !!!!

[Noak]

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
	)
)

[Sean]

@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/

[SHDJason]

@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. 

[v-sihou-msft · 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,

[SamiTi]

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 

[Sean]

@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

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