Forum Discussion

ashishg's avatar
ashishg
Icon for Advocate I rankAdvocate I
3 years ago

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

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

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

    • ashishg's avatar
      ashishg
      Icon for Advocate I rankAdvocate I
      3 years ago

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

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        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 )