Get TotalSales base on Selected Month

[Anonymous]

I have a MonthSelection table which contain MonthDate and this doesn't have any relationships (Standalone table). 

Also, I have SalesTbl table which contains MonthDate, Product, Sales and Cost, and a DateTbl containing MonthDate, Year, MonthName. SalesTbl and DateTbl has relationship base on MonthDate. 

I have a measures

TotalSales = Sum(SalesTbl[Sales])

I created a table visual that have Product and MonthSelection Slicers

My question is how can I calculate TotalSales to show data greater than or equal to Month selected in the MonthSelection Slicer as shown below

I tried using this measure 

TotalSales based on Monthselected = CALCULATE(Sum(SalesTbl[Sales]), FILTER(ALL(DateTbl), DateTbl[MonthDate] = SELECTEDVALUE(MonthSelection[MonthDate])

but didn't work.

How can i achieve this?

[V-pazhen-msft · Accepted solution]

@Anonymous 

Check the pbix for detail.

The monthdate column must be Date type.

1. Create a separated and disconnected table and use it as slicer: Slicer = DISTINCT( 'Table'[MonthDate] )

2. Create a measure to get the sales later than the selected month date:

Sales = CALCULATE(SUM([Total Sales]),FILTER('Table',[MonthDate]>=SELECTEDVALUE(Slicer[MonthDate])))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[CNENFRNL]

[CNENFRNL]

In fact, use a disconnected table as slicer is way much easier to author a measure; I keep the relationship on purpose to force myself to keep a clear mind over filter modifications during the calculation.

[Anonymous]

@CNENFRNL 

Thanks for the reply and pbix which works fine.

but i tried to used that dax for my real scenario and the DAX i used is

measure =
VAR BaseMonthselected = SELECTEDVALUE(MonthSelectionTable[EndMonth])
RETURN
IF(
CALCULATE(  HASONEVALUE(CalendarBridgeTable[EndMonth]), REMOVEFILTERS(MonthSelectionTable[EndMonth])
),
IF(
CALCULATE(MAx(CalendarTable[EndMonth]), REMOVEFILTERS(MonthSelectionTable[EndMonth])) >= BaseMonthselected,
CALCULATE( [TotalBase],REMOVEFILTERS(MonthSelectionTable[EndMonth]))
),
SUMX(  FILTER(
ALLSELECTED(CalendarBridgeTable[EndMonth]),
CalendarTable[EndMonth] >= BaseMonthselected
),
CALCULATE( [TotalBase],REMOVEFILTERS(MonthSelectionTable[EndMonth]))
) )

This didn't work for me. How can I just the dax to work properly.

Note:

the CalendarTable[EndMonth] is on the visual,

MonthSelectionTable[EndMonth] is on the slicer and is not connected or have any relationship,

[Total Base] is the measure that is already calculated.
Thanks

[mh2587]

please replace FILTER(ALL(DateTbl) with FILTER(ALLSELECTED(DateTbl) hope this will give desired output

[Anonymous]

@mh2587 I change it to FILTER(ALLSELECTED(DateTbl) but it didn't work