Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RemiAnthonise
Helper V
Helper V

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

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.

View solution in original post

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

View solution in original post

12 REPLIES 12
RemiAnthonise
Helper V
Helper V

@gooranga1 Yes, this did it! Thanks a lot, you made my day.

abhi9255
Helper II
Helper II

Can you try replacing semicolons with comma in your formula.

@abhi9255 Unfortunately, that doesn't work.

error.jpg

Can you try this

 

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

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

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.

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

url

 

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

 

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.

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.

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

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

v-jiascu-msft
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.