Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ashishg
Advocate I
Advocate I

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

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 YearSales
Jan-1710
Feb-1720
Mar-1730
Apr-1740
May-1750
Jun-1760
Jul-1770
Aug-1780
Sep-1790
Oct-17100
Nov-17110
Dec-17120
Jan-18130
Feb-18140
Mar-18150
Apr-18160
May-18170
Jun-18180
4 REPLIES 4
johnt75
Super User
Super User

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

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

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 )

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors