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.
Hi all
I'm trying to recreate this table in power BI but no matter how I try I can not calculate the diffence between two months ( and after I will need to calcualte the %).
so for exemple I need to do: "sale of jan"- "sale of feb)
thank you for the help
do you have a proper dimdate table ?
if yes,
you can simply use time intelligence functions .
like example :
p_month =
calculate ( sum(table_name[ col_name]) , previousmonth(dimdate[date]) )
or
calculate ( sum(table_name[ col_name]) , dateadd(dimdate[date],-1,month) )
if no,
you need to use window function, . offset DAX function will do the trick.
f my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
i tried this calculations but they are not showing anything.. maybe an issue with the date? sorry I'm a rather new user
you need to have a proper dimdate
and you need to set it as date table .
create proper dimdate : https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query
datetime intelligence functions : https://dax.guide/dateadd/
or try the following code :
measure =
var current_value = sum( table_name[sales_col_name])
var datasoure =
offset(
-1,
calculatetable(
summarize (
tbl,
tbl[year],
tbl[month]
),
removefilters ( tbl[month] )
),
orderby( tbl_name[year_col] , asc , tbl_name[month_col] , asc )
)
var prev_month =
calculate (
sum( table_name[sales_col_name]),
datasoure
)
return
(current_value - prev_month) / prev_month
let me know if this works for you .
if my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution !✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Try something like this, dont forget to apply necessary changes like column names
SalesDifference =
VAR CurrentMonthSales = CALCULATE(SUM('YourTable'[Sales]),
FILTER(ALL('YourTable'[Month]),
'YourTable'[Month] = MAX('YourTable'[Month])))
VAR NextMonthSales = CALCULATE(SUM('YourTable'[Sales]),
FILTER(ALL('YourTable'[Month]),
'YourTable'[Month] = MAX('YourTable'[Month]) + 1))
RETURN
NextMonthSales - CurrentMonthSales
Hello @Raffa , can you please share the measure that you are using for this. You have to use PREVIOUS MONTH DAX function to achieve the same.
Better create a calendar table and connect it with the fact table.
>> From Calendar table get dates.
>> From fact table get the values
>> From measure using CALCULATE(SUM(VALUES),PREVIOUSMONTH('DATE TABLE'[DATE_COLUMNS])) calculate MOM
If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!
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 |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
80 | |
67 |