Forum Discussion
Measure does not total
Hi everyone,
I have the below formula to calculate 1st Month's sales.
1st Month's Sales =
CALCULATE (
SUM( 'Total-Combined'[Amount] ),
FILTER (
ALL ( 'Total-Combined'[Table-Dates.Date] ),
'Total-Combined'[Table-Dates.Date] > DATE ( YEAR ( MIN ( 'Total-Combined'[Table-Dates.Date] ) ), MONTH ( MIN ( 'Total-Combined'[Table-Dates.Date] ) ) + 0, 1 ) &&
'Total-Combined'[Table-Dates.Date] < DATE ( YEAR ( MIN ( 'Total-Combined'[Table-Dates.Date] ) ), MONTH ( MIN ( 'Total-Combined'[Table-Dates.Date] ) ) + 1, 1 )
)
)
However, when this is broken down by products in a table, the sum of the measures do not add correctly in the table . How do I change my formula so that the table displays the total correctly?
3 Replies
Hi,
Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table. Write these measures
Total = sum('Total-Combined'[Amount])
Sale in first month = calculate([Total],datesbetween(Calendar[Date],min(Data[Date]),eomonth(min(Data[Date]),0)))
Hope this helps.
Thankyou Ashish, what if I wanted the 2nd month's sales and the 3rd month's sales?
You are welcome. Share data in a format that can be pasted in an MS Excel file and show the expected result.
