Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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?
Solved! Go to Solution.
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.
Can you try replacing semicolons with comma in your formula.
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
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.
observe the formula which i have provided in your screenshot I can see semicolons.
Hi @RemiAnthonise,
Can you share a dummy pbix file?
Did you create proper relationships?
Best Regards,
Dale
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |