topic Need Help for creating Month over Month % in Direct Query in PBI in DAX Commands and Tips

Need Help for creating Month over Month % in Direct Query in PBI

ashishg — Thu, 13 Jul 2023 10:34:50 GMT

Hello Guys,
Need help !!!

I will be using DIrect query in power bi and my data source is azure synapse.
i need to calculate month over month % in desktop.
I have order date and sales. so based on order date o create one calculated column as Order Month Year to show in column chart.
when I try to use below dax using dateadd, previous month the value shows for mom% is blank.

mom% =
var currentmonth = sum(sales)
var previousmonth = calculate(sum(Sales), dateadd(orderdate, -1, month)
return
divide((currentmonth - previousmonth), previousmonth)

in the place of dateadd i used previous month but it gives me black value also when i try to only create previous month sales and added that measure with my order year month column it gives me blank.

Note- when I'm using Order Date column not the year Month the pure date then the values is appeared

I don't know why this happening. will you help me

Sample data:

Order Month Year	Sales
Jan-17	10
Feb-17	20
Mar-17	30
Apr-17	40
May-17	50
Jun-17	60
Jul-17	70
Aug-17	80
Sep-17	90
Oct-17	100
Nov-17	110
Dec-17	120
Jan-18	130
Feb-18	140
Mar-18	150
Apr-18	160
May-18	170
Jun-18	180

Re: Need Help for creating Month over Month % in Direct Query in PBI

johnt75 — Thu, 13 Jul 2023 11:19:24 GMT

You need to use REMOVEFILTERS on whichever table / column you are using in the visuals.

Re: Need Help for creating Month over Month % in Direct Query in PBI

ashishg — Thu, 13 Jul 2023 13:21:39 GMT

Can you please help me with DAX, or how we can modified above dax

Re: Need Help for creating Month over Month % in Direct Query in PBI

johnt75 — Thu, 13 Jul 2023 13:26:35 GMT

If you're using columns from the date table in your visuals you could do something like

MoM % = VAR CurrentMonth = SUM ( Sales[Amount] ) VAR PrevMonth = CALCULATE ( SUM ( Sales[Amount] ), DATEADD ( Sales[Order Date], -1, MONTH ), REMOVEFILTERS ( 'Date' ) ) RETURN DIVIDE ( CurrentMonth - PrevMonth, PrevMonth )

Re: Need Help for creating Month over Month % in Direct Query in PBI

ashishg — Fri, 14 Jul 2023 07:06:22 GMT

This was I'm doing but when I used my calculated column( month year like: Jan 2023) then the value shows the blank.