cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

DAX: Previousmonth / parallelperiod / dateadd doesn't work

I've made a table and I've added some fields to calculate total sales per month and Month over month compare, see image below.

total sales.jpg

 

I've used these formulas:

Total Sales = SUM(ProjectPostTransViews[TotalSalesAmount])

 

Total Sales vorige maand = CALCULATE(ProjectPostTransViews[Total Sales]; PARALLELPERIOD('Calendar'[Date]; -1; MONTH))

Total Sales vorige maand2 = CALCULATE(ProjectPostTransViews[Total Sales]; PREVIOUSMONTH('Calendar'[Date]))

Total Sales vorige maand3 = CALCULATE(ProjectPostTransViews[Total Sales]; DATEADD('Calendar'[Date]; -1; MONTH))

 

Calendar table = Calendar = CALENDAR (DATE (2016; 01; 01); DATE (2025; 12; 31))

 

I've treied the 3 formulas to see if, in some case, the outcome is different. It's not. But that's not the problem. My problem is that the values for all 3 'last month' formulas are the same as the total sales value for that month. 

What am I doing wrong?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
gooranga1 Senior Member
Senior Member

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

Hi @RemiAnthonise,

 

It looks like it's the TRansactionDate in your ProjectPostTransViews as it's a datetime field with a time of 12:00:00 midday. So when you link the Calendar table to it it will return no values as these are just the date that will assume midnight.

 

Create a new column in the ProjectPostTransViews table like;

 

TransactionDateOnly = date(year(ProjectPostTransViews[TransactionDate]),month(ProjectPostTransViews[TransactionDate]),DAY(ProjectPostTransViews[TransactionDate]))

then join on that.

gooranga1 Senior Member
Senior Member

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

And you will need to change what you grouping by in your matrix. So either create a new column on Calendar that is "maand nummer" or group by Jaar and Maand from Calendar.

 

CapturePeriod.PNG

12 REPLIES 12
Community Support Team
Community Support Team

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

Hi @RemiAnthonise,

 

Can you share a dummy pbix file?

Did you create proper relationships?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
abhi9255 Regular Visitor
Regular Visitor

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

Can you try replacing semicolons with comma in your formula.

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

@abhi9255 Unfortunately, that doesn't work.

error.jpg

abhi9255 Regular Visitor
Regular Visitor

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

Can you try this

 

Total Sales vorige maand3 = CALCULATE(ProjectPostTransViews[Total Sales],DATEADD('Calendar'[Date], -1, MONTH))

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

@abhi9255 That's what I did, see the yellow bar with the corresponding error.

Currently, I'm checking my relationships between the tabels but I can't find something.

abhi9255 Regular Visitor
Regular Visitor

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

observe the formula which i have provided in your screenshot I can see semicolons.

Community Support Team
Community Support Team

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

Hi @RemiAnthonise,

 

I wonder if you can share the file. You can keep the minimal size of the file that can show us the issue. You also can use a dummy file. Anyway, please keep your private data safe.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

@v-jiascu-msft I think this will work.

url

 

Data is testdata so it's okay to share public. Thanks!

 

Re: DAX: Previousmonth / parallelperiod / dateadd doesn't work

In case someone had some time to check my .pbix, do you have any solution? I think it has something to do with the date table but I can't find the problem.