cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculated Column of Monthly Sales Total

I would like to have a calculated column of the monthly sum of sales like this:

 DateTime Sales MonthlySales 1/1/2018 10 100 1/5/2018 90 100 2/16/2018 25 50 2/18/2018 25 50

I've tried to achieve this by using the following formula:

```MonthlySalesTotal =
SUMX(
VALUES(DimDate[MonthYear]),
SUM(FactSales[LineTotal])
)```

This just gives me the entire sales total on each line like so:

 DateTime Sales MonthlySales 1/1/2018 10 150 1/5/2018 90 150 2/16/2018 25 150 2/18/2018 25 150

It looks like it's failing to group by MonthYear correctly and just providing all the MonthYear values instead of the single on related to that row. I have confirmed the join between my calendar table and sales table since formulas such as TOTALYTD work just fine. What formula can I use to produce a monthly sales total for each line in my table?

1 ACCEPTED SOLUTION

Accepted Solutions
Impactful Individual

## Re: Calculated Column of Monthly Sales Total

You won't be able to use it as a report filter unfortunately. You could create a column by duplicating the table in power query and aggregate the table to calculate the monthly values and merge join the table to give you a value.

You would need to join on the month start date which you could calculate in M.

`Text.From(Date.Year([DateTime])) & "-" & Text.End("0" &Text.From(Date.Month([DateTime])),2) & "-01"`

Copy that table and then group the new table;

Then merge join on the original table

Then you will have the monthly values in a column. Be careful to not double count the values it's purely for filtering purposes only!

4 REPLIES 4
Highlighted
Impactful Individual

## Re: Calculated Column of Monthly Sales Total

`Measure = CALCULATE(sum(Table16[Sales]),filter(ALLSELECTED(Table16),Table16[DateTime].[MonthNo] = maxx(Table16,Table16[DateTime].[MonthNo]) && Table16[DateTime].[Year] = maxx( Table16,Table16[DateTime].[Year])))`

You could try above.

Frequent Visitor

## Re: Calculated Column of Monthly Sales Total

That got me much closer! My formula below works as a measure but I can't use it as a report level filter. I tried using the same code for a caculated column and it just shows all blanks. Is there a way to write it as cacluated column so I can use it across all visuals in the report without manually dragging the measure into each individual visual filter?

(Or alternatively is there a way to use this measure as a report level filter)

```MonthSalesTotal =
CALCULATE(sum(FactSales[LineTotal]),
filter(ALLSELECTED(DimDate),
DimDate[MonthYear]= maxx(DimDate,DimDate[MonthYear]))
)```
Impactful Individual

## Re: Calculated Column of Monthly Sales Total

You won't be able to use it as a report filter unfortunately. You could create a column by duplicating the table in power query and aggregate the table to calculate the monthly values and merge join the table to give you a value.

You would need to join on the month start date which you could calculate in M.

`Text.From(Date.Year([DateTime])) & "-" & Text.End("0" &Text.From(Date.Month([DateTime])),2) & "-01"`

Copy that table and then group the new table;

Then merge join on the original table

Then you will have the monthly values in a column. Be careful to not double count the values it's purely for filtering purposes only!

Frequent Visitor

## Re: Calculated Column of Monthly Sales Total

Thanks for the detailed answer.... but that's a really long walk for just a monthly sum column. I think at this point I will give up trying to do this in DAX and just modify the underlying SQL View. Thanks for the help!

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors