Forum Discussion

ashmozzart's avatar
ashmozzart
Frequent Visitor
9 years ago

MOM and QOQ Filters Calculation

Hi, I have a graph which compares Customer Name on the Y_Axis and SalesAmount on the X_Axis.They need a MOM and QOQ filters.What exactly QOQ/MOM i am referring is when i select a month in the filter...
Need Help
MattAllington's avatar
MattAllington
Community Champion
9 years ago

You can use the dateadd function like this

 

Total Sales = sum(table[sales column])

total sales prior month = calculate([total sales], dateadd(calendar[date],-1,month))

 

you need a calendar table http://exceleratorbi.com.au/power-pivot-calendar-tables/

 

the quarter calc depends what you need (e.g. Is it a rolling quarter or fixed quarter?)

 

total sales prior quarter = calculate([total sales], dateadd(calendar[date],-1,quarter))

ashmozzart's avatar
ashmozzart
Frequent Visitor
9 years ago

Hi,

Thanks a lot for the reply.

When i created a derived column using below Dax,

PreviousOrders_ = CALCULATE(SUM(Sheet1[OrderAmount]),DATEADD(Sheet1[BillStartDate],-1,MONTH))

I am getting an empty with no data.May i know why?

 

 

  • MattAllington's avatar
    MattAllington
    Community Champion
    9 years ago

    This should be written as a measure, not a column. 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi ashmozzart,

     

    As most Time Intelligence Functions (DAX) require an individual and consecutive Date table to work correctly, you may need create it using CALENDAR or CALENDARAUTO Function (DAX) if you don't have yet. And create a relationship between the Date table and your fact table with the Date column.

     

    Assume your Date table is called "DateTable", then the formula to create the measure should like below.

    PreviousOrders_ =
CALCULATE (
    SUM ( Sheet1[OrderAmount] ),
    DATEADD ( DateTable[Date], -1, MONTH )
)

     

    Regards