topic compare sum of sales for this period of this month vs the same period of the previous month in DAX Commands and Tips

compare sum of sales for this period of this month vs the same period of the previous month

Anonymous — Wed, 19 Oct 2022 11:29:53 GMT

Hello,

In my report, I calculated a comparison measure of the sales of a month compared to the previous month.

Variation = VAR __PREV_MONTH = CALCULATE( SUM('Table 1'[Sales]), DATEADD('Table 1'[Date].[Date], -1, MONTH) ) RETURN DIVIDE(SUM('Table 1'[Sales]) - __PREV_MONTH, __PREV_MONTH) )

The concern is that if we are in the current month, the result is not correct since it is a comparison of a non-complete month compared to a complete month.
In this case, how to do if we are in the current month, to compare the elapsed days of the current month compared to the same period of the previous month.
(Example: today is 10/19/2022, I am comparing sales from 10/01/2022 to 10/19/2022 vs sales from 09/01/2022 to 09/19/2022)

Thanks for your help

Re: compare sum of sales for this period of this month vs the same period of the previous month

daXtreme — Wed, 19 Oct 2022 23:48:05 GMT

// First and foremost, you should create base measures // from which all else will follow. So, for instance: [Total Sales] = SUM('YourFactTable'[Sales] ) // Second, please give your tables decent, pronounceable // and descriptive (as well as short) names. A name like // 'Table 1' is not something you'd like the consumers // of your model to see. It's not informative in the slightest. // Third, you should always build a proper model with // dimensions and fact tables. No auto-generated hierarchies // please as these will be returning wrong results before // you even know it. [Variation] = var ThisMonthSales = [Total Sales] var LastDateWithSales = CALCULATE( MAX( YourFactTable[Date] ), REMOVEFILTERS( ) ) var PrevMonthAdjusted = DATEADD( CALCULATETABLE( VALUES( 'Calendar'[Date] ), 'Calendar'[Date] <= LastDateWithSales, VALUES( 'Calendar'[Date] ), REMOVEFILTERS( 'Calendar' ) ) -1, MONTH ) VAR PrevMonthSales = CALCULATE( [Total Sales], PrevMonthAdjusted, // This last line is not needed if 'Calendar' // is marked in the model as a Date Table. REMOVEFILTERS( 'Calendar' ) ) var Output = DIVIDE( ThisMonthSales - PrevMonthSales, PrevMonthSales ) return Output

If you want to learn about building correct and fast models and why it's of crucial importance to build a star-schema model, you can read THIS. Do not fall into the traps that so many have before you.

You need something like this:

Re: compare sum of sales for this period of this month vs the same period of the previous month

Anonymous — Thu, 20 Oct 2022 13:40:07 GMT

Hi daXtreme ,

I realy appreciated the way you answered me. You were not content only to give me an answer to my question but also to give me advice.

Thank you