Monthly delta measures/calculated column

[Anonymous]

Hi community,

Need help to solve this.
I have this data.

D	Name	Month	Sales
1	Joe	December 2021	3000
1	Joe	January 2022	5000
1	Joe	February 2022	5000
2	Imran	December 2021	2000
2	Imran	January 2022	8000
2	Imran	February 2022	7000
3	Natalie	December 2021	6000
3	Natalie	January 2022	9000
3	Natalie	February 2022	3000

How can i have a calculate column or a measure of monthly delta which is each month sales minus december 2021 sales. The december act as a baseline.

Thanks.

[v-yalanwu-msft]

Hi, @Anonymous ;

You could create a measure :

Measure = 
var _per= CALCULATE(SUM([Sales]),FILTER(ALL('Table'),EOMONTH([Month],0)=EOMONTH(MAX([Month]),0)))
var _12last=CALCULATE(SUM([Sales]),FILTER(ALL('Table'),[Month].[Year]=2021&&[Month].[MonthNo]=12))
return _per-_12last

Or a column.

Column = 
var _per= CALCULATE(SUM([Sales]),FILTER('Table',EOMONTH([Month],0)=EOMONTH(EARLIER([Month]),0)))
var _12last=CALCULATE(SUM([Sales]),FILTER('Table',[Month].[Year]=2021&&[Month].[MonthNo]=12))
return _per-_12last

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@v-yingjl why have you accepted @v-yalanwu-msft 's solution please? I posted a very similar solution 4 days before @v-yalanwu-msft  and I purposefully did NOT include a way to create a calculated column, as that is not the best practice solution and will make the data model larger than it needs to be. 

From my knowledge, my solution is simpler and more efficient, but I'm happy to learn, so please curious why you chose @v-yalanwu-msft 's solution. Thanks!

[Anonymous]

Tried these:

Total Sales = SUM ( Sales[Sales] )

Dec 2021 Sales = CALCULATE ( [Total Sales] , Date[Month Year] = "Dec 2021" )

Diff = [Total Sales] - [Dec 2021 Sales]

But the Dec 2021 Sale measure,  return blank for all.

[AllisonKennedy]

@Anonymous  What does your Dim Date table look like? You must have a Text field for Month Year that exactly says "Dec 2021", if it says anything different this will not work and you'll have to change to what your date table looks like. 

[AllisonKennedy · Accepted solution]

@Anonymous  Create a measure for Dec 2021, then subtract that from [Total Sales] and put it in a visual by month Year:

Total Sales = SUM ( Sales[Sales] )

Dec 2021 Sales = CALCULATE ( [Total Sales] , Date[Month Year] = "Dec 2021" )

Diff = [Total Sales] - [Dec 2021 Sales]

[Anonymous]

Thanks @AllisonKennedy  for the response,

I've tried your solution,

The, Date[Month Year] in Dec 2021 Sales = CALCULATE ( [Total Sales] , Date[Month Year] = "December 2021" ) is from date table right?

I did all the step you shows in creating the three measure.

But when I visualize the ID, Month Year, Sales Delta/Diff this error happens, it says about this measure(Dec 2021 Sales) got an error because we are comparing date with text.

I think it is because this column Date[Month Year] is date type but when we filtering we put, Date[Month Year] = "December 2021" make the value compared to is a text.

do you know how could we fix this?

[AllisonKennedy]

@Anonymous  Yes, the Date[Month Year] should be from your date table and should be a new column, not the Date column. This column should be text that groups the Month and Year from your Date. You can add this in Power Query.  Select the Month name column and Year column using the Ctrl key, then Click the Add Column tab > Merge Columns. Use Space as a separator. Name this column Month Year. Then use it in the measure. It will be text data type.