Forum Discussion

Angads's avatar
Angads
Advocate I
3 years ago

Help with dax!

I want if select all is selected in date slicer it should show total sales for all dates, if no date selected it should show last date sales in date column and id any specific date selected it should show sales for that date.
There is no string "select all" in date column. And we can't compare date with string.
these are limitations please give proper result..

Values on selection = IF(ISFILTERED(Sheet1[Date]),CALCULATE([Sum of Values]),IF(NOT ISFILTERED(Sheet1[Date]),CALCULATE([Sum of Values],LASTDATE(Sheet1[Date]))))

with the above formula I am able to achieve  if no date selected it should show last date sales in date column and id any specific date selected it should show sales for that date.


Any help will be great.

13 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Angads 
    Please try

    Values on selection =
SUMX ( TOPN ( 1, Sheet1, Sheet1[Date] ), Sheet1[Value] )
    • Angads's avatar
      Angads
      Advocate I
      3 years ago

      Its giving me sales for selected date only. I am not getting answer for multiple dates select with above formuale also I want total sales when I select "select all" option in slicer. tamerj1 

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        Angads 
        Better to present a sample with the expected results as this new explanation does not match with the first one. If I follow your new explanation literally then just use SUM ( Sheet1[Value] )

  • devanshi's avatar
    devanshi
    Helper V
    3 years ago

     Totalsales = SUM([Sales])

    VAR lastdatesvalues = CALCULATE(SUM([Sales]),LASTDATE(Date table))

    VAR selected date = IF(ISFITHERED(Date table),

                                      IF(Date table)=BLANK(),lastdates),SUM([Sale]))

    RETURN 

    Totalsales