Forum Discussion

Budfudder's avatar
Budfudder
Icon for Helper IV rankHelper IV
7 years ago

Ignore 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:

 

 

This Year NB To Date = 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.

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee

    Hi Budfudder

     

    You may create a calendar table and use it as slicer. Make sure there is no relationship between it and Tx table as below. Then you may get the value with your measure as requested.

    Date Selected = SELECTEDVALUE('Calendar'[Date])

     

    Regards,

    Cherie