Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |