Forum Discussion

Budfudder's avatar
Budfudder
Helper IV
7 years ago
Solved

Ignoring A Slicer?

I have a simple table visual showing a list of transactions, each row of which has a date and a value (a dollar amount). I have a slicer which allows the user to select a date, and the table correctly shows only the transactions for that date.

 

I now have a requirement to show the Year To Date figures - so whatever date the user chooses in the slicer, he'll see a separate display of the total of the transactions this year up to and including the chosen date.

 

How can I calculate the total of the transactions up to and including the selected date? I created a simple calculation:

 

 

YTD = sumx(filter(Tx,
                  Tx[Created On] <= [Date Selected] && Tx[Created On] >= DATE(2018,1,1)),
           Tx[Value])

Note that the "[Date Selected]" item is just a variable holding the date selected in the slicer.

 

But the slicer stops that calculation from seeing all the dates - it just gives the total for the date selected in the slicer, not for the whole year up to and including that point.

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Budfudder,

     

    Based on your formulas, please change the formula like below.

     

    Measure =
VAR t = [Date Selected]
RETURN
    CALCULATE (
        SUM ( 'Tx'[Amount] ),
        FILTER ( ALL ( Tx ), 'Tx'[Date] <= t && 'Tx'[Date] >= DATE ( 2018, 1, 1 ) )
    )

    Here is the result.

     

     

    Best  Regards,

    Cherry

6 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Budfudder,

     

    If I understand your scenario correctly that you want to show the YTD value based on the date slicer?

     

    If it is, you could modify your fomula like below.

     

     

    sumx(filter(ALL(Tx),
                  Tx[Date] <= SELECTEDVALUE(Tx[Date]) && Tx[Date] >= DATE(2018,1,1)),
           Tx[Amount])

    Here is my test result.

     

     

     

    If you still need help, please share some data sample and your desired output so that we could help further on it.

     

    Best Regards,

    Cherry

    • Budfudder's avatar
      Budfudder
      Helper IV
      7 years ago

      Hi Cherry, thanks for your suggestion. Unfortunately when I do as you suggest, the statement just ignores the selection made in the slicer and gives me the total for all entries in the table, regardless of their date. Note that it works if I user date literals. For example:

      sumx(filter(ALL(Tx),
                  Tx[Date] <= DATE(2018,11,1) && Tx[Date] >= DATE(2018,1,1)),
           Tx[Amount])

      works perfectly. This makes me think it's something in the way I'm getting the value from the date slicer. And that's a bit complex. The date slicer is actually created like this:

      SlicerDate = IF('Tx'[Created On]=MAX('Tx'[Created On]),
                "Last Business Day",
                FORMAT('Tx'[Created On], "YYYY.MM.DD"))

      That gives a slicer showing dates in the YYYY.MM.DD format, and the most recent day says "Last Business Day" instead of the actual date. It auto-updates each day to have the Last Busines Day value selected by default.

       

      Then, to get the actual value of the date selected in the slicer, I use:

      Date Selected = 

VAR SelectionText = SELECTEDVALUE(Tx[SlicerDate])

RETURN

    IF(NOT(ISFILTERED('Tx'[SlicerDate])),
           max('Tx'[Created On]),
           IF(COUNTROWS(ALLSELECTED('Tx'[SlicerDate])) = 1, 
              IF(SelectionText = "Last Business Day",
                 max('Tx'[Created On]),
              DATE(left(SelectionText, 
                        4),
                   mid(SelectionText,
                       6,
                       2),
                   right(SelectionText,
                         2))),
              max('Tx'[Created On])))

      That gets the date selected as a date, rather than just as text. As a test I display the value of that field, and it's correctly displaying. Yet when I use that field in the SUMX formula, it doesn't work.

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Budfudder,

         

        Based on your formulas, please change the formula like below.

         

        Measure =
VAR t = [Date Selected]
RETURN
    CALCULATE (
        SUM ( 'Tx'[Amount] ),
        FILTER ( ALL ( Tx ), 'Tx'[Date] <= t && 'Tx'[Date] >= DATE ( 2018, 1, 1 ) )
    )

        Here is the result.

         

         

        Best  Regards,

        Cherry

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Create a Calendar Table and build a relationship from the Date column of the Tx Table to the Date column of the Calendar Table.  In the slicer, drag the Date from the Calendar Table.  Write this measure

     

    =CALCULATE(SUM(Data[Value]),DATESBETWEEN(Calendar[Date],DATE(2018,1,1),MAX(Calendar[Date])))

     

    Hope this helps.