difference %

[Raffa]

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

 

[Daniel29195]

@Raffa 

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! 🤠

[Raffa]

i tried this calculations but they are not showing anything.. maybe an issue with the date? sorry I'm a rather new user

[Daniel29195]

@Raffa 

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! 🤠

[MAwwad]

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

[Kishore_KVN]

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!!