none
Total net Sales - If no sales for a specific month, takes last year sales for this specific month

    Question

  • Hello,

    The Sales This year row takes the last year sales for this specific month if there is no sales record for the actual year yet.

    The measure per month is working well. However, the Grand total only add the sales for the current year and omit the Mar Sales.

    Here is my DAX formula to get the Sales This year measure:

    =IF(SUM(Sales_level4[PN10])>0,SUM(Sales_level4[PN10]),CALCULATE(SUM(Sales_level4[PN10]),FILTER(ALL('Calendar'),'Calendar'[Year]=MAX('Calendar'[Year])-1&&'Calendar'[Month number]=MAX('Calendar'[Month number]))))

    I understand I should make a specific formula where the filter for the month is not active with a SUMX to get the actual year sales + the last year sales for the month having no sales for the actual year but not sure how to achieve it.

    Any idea?

    Here is the table:

    - Sales This year for Mar is at 2000 because it takes the amount from Sales Last year

    - Grand total only sums Sales This year for Jan and Feb and ignore the value in Mar.

    Row Labels Jan Feb Mar Grand Total
    Sales This year 1000 1200 2000 2200
    Sales Last year 900 1100 2000 4000

    Thanks.

    Tuesday, March 21, 2017 12:51 AM

All replies

  • I think I actually got it.

    I decomposed my formula as follow:

    Sales per month TY:

    =CALCULATE(SUM(Sales_level4[PN10]),FILTER(ALL('Calendar'),'Calendar'[Year]=MAX('Calendar'[Year])&&'Calendar'[Month number]=MAX('Calendar'[Month number])))

    Sales per month LY:

    CALCULATE(SUM(Sales_level4[PN10]),FILTER(ALL('Calendar'),'Calendar'[Year]=MAX('Calendar'[Year])-1&&'Calendar'[Month number]=MAX('Calendar'[Month number])))

    Total sales checking if the month column is filtered and using sumx:

    IF(ISFILTERED('Calendar'[Month]),

    IF([Sales per month TY]>0,

    [Sales per month TY],

    [Sales per month LY]),

    SUMX(VALUES('Calendar'[Month number]),IF([Sales per month TY]=0,[Sales per month LY],[Sales per month TY]))
    )

    Tuesday, March 21, 2017 1:20 AM
  • Congratulations, you've solved your problem!

    Indeed, the key here is to use SUMX to iterate over the months, which enables you to do a calculation by month instead of the whole year at once (your first attempt didn't work because sales for the whole year is larger than zero anyway).

    That said, you can optimize your formula in a couple of ways:

    Sales per month TY is really just SUM(Sales_level4[PN10]) when applied in a context of one month (which you do)

    Sales per month LY can be solved using a time intelligence function: CALCULATE(SUM(Sales_level4[PN10]), SAMEPERIODLASTYEAR('Calendar'[Date]))

    You can remove the whole IS(ISFILTERED(...) part of the last formula, as SUMX will iterate over a single month when the context is that month only (as a matter of fact, your measure returns a wrong result when someone selects two months: only the results for the last one are returned). Also, take care when comparing a result to 0: normally when there's no sales in a month, the result is BLANK, not zero.

    So your formula can be as simple as:

    SUMX(VALUES('Calendar'[Month number]), IF([Sales per month TY]>0, [Sales per month TY], [Sales per month LY]))

    Tuesday, March 21, 2017 1:48 PM
    Answerer
  • Hi sbkl,

    Glad to hear that your issue got solved. Please mark corresponding reply which will help other people find the solution easily. 

    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 22, 2017 1:33 AM
    Moderator