cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BeatNick
Frequent Visitor

Calculated Column of Monthly Sales Total

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

 

DateTimeSalesMonthlySales
1/1/201810100
1/5/201890100
2/16/20182550
2/18/20182550

 

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:

DateTimeSalesMonthlySales
1/1/201810150
1/5/201890150
2/16/201825150
2/18/201825150

 

 

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
gooranga1 Impactful Individual
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"

mlanguage.PNG

 

Copy that table and then group the new table;

 

mlanguage1.PNG

 

Then merge join on the original table

 

mlanguage2.PNG

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!

 

mlanguage3.PNG

View solution in original post

4 REPLIES 4
gooranga1 Impactful Individual
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.

 

measure.PNG

 

Highlighted
BeatNick
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]))
)
gooranga1 Impactful Individual
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"

mlanguage.PNG

 

Copy that table and then group the new table;

 

mlanguage1.PNG

 

Then merge join on the original table

 

mlanguage2.PNG

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!

 

mlanguage3.PNG

View solution in original post

BeatNick
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!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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