locked
YTD Sales Amount Measure - Calculating wrong field RRS feed

  • Question

  • Greeting everyone,

    I'm kindly asking for some guidance. I've created four measures to help me calculate YTD Sales.How to get "YTD Sales" use the measure I'm specifiying it.

     YTD Sales (Balance Change) is summing up measure "Current Month Sales" instead of the one I'm specifiying which is Current Month Sales - Previous Month Sales (Balance Change).

    Here are the measure created.

    • Current Month Sales 
    • Previous Month Sales 
    • Current Month Sales - Previous Month Sales (Balance Change)
    • YTD Sales (Balance Change)


    This is the output ....which YTD Sales BC is wrong!

    but the output for YTD Sales BC should look like this

    YTD Sales (Balance Change) = CALCULATE (
      [Current Month Sales - Previous Month Sales (Balance Change)],
        FILTER (
            ALL ( Dates ),
            Dates[Date] <= MAX ( Dates[Date] )
                && Dates[year] = MAX ( Dates[year] )
        )
    )
    Current Month Sales = CALCULATE(SUM(Additional_Info[C Sales Amount]))


    Previous Month Sales = 
    VAR CurrentMonth = SELECTEDVALUE('Calendar'[MonthOfYear]) 
    VAR CurrentYear = SELECTEDVALUE('Calendar'[Year]) 
    VAR MaxMonthNum = CALCULATE(MAX('Calendar'[MonthOfYear]),All ('Calendar')) 
    	
    	RETURN IF(HASONEVALUE('Calendar'[MonthOfYear]), 
    			SUMX(FILTER(ALL('Calendar'), 
    					IF(CurrentMonth = 1, 'Calendar'[MonthOfYear] = MaxMonthNum && 'Calendar'[Year] = CurrentYear-1,
    						'Calendar'[MonthOfYear] = CurrentMonth - 1 && 'Calendar'[Year] = CurrentYear)),
    							[Current Month Sales]), 
    	BLANK())


    Current Month Sales - Previous Month Sales BC = IF(RIGHT(MAX('Calendar'[Period]),2) = "01", 
    							CALCULATE([Current Month Sales]),
    							CALCULATE([Current Month Sales] - [Previous Month Sales]))



    Any help is appreciated. Thank you!

    Jeannette




    • Edited by Jeannette_81 Tuesday, April 24, 2018 7:53 PM Organizing content
    Tuesday, April 24, 2018 4:03 PM

Answers

  • Your 2 issues are related. The missing total for the previous month calculation happens because you have an IF HASONEVALUE() check in your measure and at the total level this returns false because there are 12 months that make up the total for a year.

    This is also what is causing the issue for your YTD balance change, because for anything after the first month there are month that one month so the IF HASONEVALUE check is returning blank so you end up just doing a YTD on the current month values.

    So you need to adjust your prior month calculation to something that will work with multiple months.

    You could do something like the following (which leverages the fact that there is no 0 month to blank out the value at month 1)

    [prior month sales for balance] =
            SUMX (
            VALUES('Calendar'[Period])
                , CALCULATE(
                [Current Month Sales]
                , VAR vPrior = 'Calendar'[Period] -1
                RETURN filter ( ALL('Calendar')
               , 'Calendar'[Period] = vPrior)
            ))



    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Jeannette_81 Wednesday, April 25, 2018 2:27 PM
    Wednesday, April 25, 2018 3:25 AM
  • Hi Jeannette,

    Thanks for your question.

    Please try below DAX formula:

    [Current Month Sales] = 
    SUM(Additional_Info[C Sales Amount])
    
    [Previous Month Sales] = 
    CALCULATE([Current Month Sales], FILTER(ALL('Calendar'),'Calendar'[Period]=MAX('Calendar'[Period])-1))
    
    [Current Month Sales - Previous Month Sales BC] = 
    SUMX(VALUES('Calendar'[Period]),[Current Month Sales]-[Previous Month Sales])
    
    [YTD Sales BC] = 
    SUMX(FILTER(ALL('Calendar'),'Calendar'[Period]<=MAX('Calendar'[Period]) &&
           LEFT('Calendar'[Period],4)=LEFT(MAX('Calendar'[Period]),4 )),
         [Current Month Sales - Previous Month Sales BC])

    As I test with below sample data, everything works as expected:

    Sumsales = 
    sum(tab[Sales])
    
    previous = 
    CALCULATE([Sumsales], FILTER(ALL(tab),tab[Weeknumber]=MAX(tab[Weeknumber])-1))
    
    Balance Change = 
    sumx(VALUES(tab[Weeknumber]),[Sumsales]-[previous])
    
    YTDSALES = 
    sumx(filter(all(tab),tab[Weeknumber]<=max(tab[Weeknumber])),tab[Balance Change])


    Best Regards
    Willson Yuan
    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, April 25, 2018 5:42 AM

All replies

  • To add to the question on why YTD Sales is using the wrong measure, I noticed there is no total for measure "Previous Month Sales."

    Tuesday, April 24, 2018 7:28 PM
  • Your 2 issues are related. The missing total for the previous month calculation happens because you have an IF HASONEVALUE() check in your measure and at the total level this returns false because there are 12 months that make up the total for a year.

    This is also what is causing the issue for your YTD balance change, because for anything after the first month there are month that one month so the IF HASONEVALUE check is returning blank so you end up just doing a YTD on the current month values.

    So you need to adjust your prior month calculation to something that will work with multiple months.

    You could do something like the following (which leverages the fact that there is no 0 month to blank out the value at month 1)

    [prior month sales for balance] =
            SUMX (
            VALUES('Calendar'[Period])
                , CALCULATE(
                [Current Month Sales]
                , VAR vPrior = 'Calendar'[Period] -1
                RETURN filter ( ALL('Calendar')
               , 'Calendar'[Period] = vPrior)
            ))



    http://darren.gosbell.com - please mark correct answers

    • Marked as answer by Jeannette_81 Wednesday, April 25, 2018 2:27 PM
    Wednesday, April 25, 2018 3:25 AM
  • Hi Jeannette,

    Thanks for your question.

    Please try below DAX formula:

    [Current Month Sales] = 
    SUM(Additional_Info[C Sales Amount])
    
    [Previous Month Sales] = 
    CALCULATE([Current Month Sales], FILTER(ALL('Calendar'),'Calendar'[Period]=MAX('Calendar'[Period])-1))
    
    [Current Month Sales - Previous Month Sales BC] = 
    SUMX(VALUES('Calendar'[Period]),[Current Month Sales]-[Previous Month Sales])
    
    [YTD Sales BC] = 
    SUMX(FILTER(ALL('Calendar'),'Calendar'[Period]<=MAX('Calendar'[Period]) &&
           LEFT('Calendar'[Period],4)=LEFT(MAX('Calendar'[Period]),4 )),
         [Current Month Sales - Previous Month Sales BC])

    As I test with below sample data, everything works as expected:

    Sumsales = 
    sum(tab[Sales])
    
    previous = 
    CALCULATE([Sumsales], FILTER(ALL(tab),tab[Weeknumber]=MAX(tab[Weeknumber])-1))
    
    Balance Change = 
    sumx(VALUES(tab[Weeknumber]),[Sumsales]-[previous])
    
    YTDSALES = 
    sumx(filter(all(tab),tab[Weeknumber]<=max(tab[Weeknumber])),tab[Balance Change])


    Best Regards
    Willson Yuan
    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, April 25, 2018 5:42 AM
  • Thank you Darren! This is exactly what I needed.
    Wednesday, April 25, 2018 2:28 PM
  • Willson,

    Thank you for your feedback. I really appreciate your help!

    Jeannette

    Wednesday, April 25, 2018 2:29 PM