Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get TotalSales base on Selected Month

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?

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

6 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

    • CNENFRNL's avatar
      CNENFRNL
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.