Measure does not total

[superbird]

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?

[Ashish_Mathur]

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.

[superbird]

Thankyou Ashish, what if I wanted the 2nd month's sales and the 3rd month's sales?

[Ashish_Mathur]

You are welcome.  Share data in a format that can be pasted in an MS Excel file and show the expected result.