Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Budfudder
Helper IV
Helper IV

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.

1 ACCEPTED SOLUTION

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.

 

result.PNG

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

tests.png

 

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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.

 

result.PNG

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I haven't had time to go through this in detail, but my premilinary results indicate that you, Cherry, are a genius. Now I've got to sit down and work out what it actually does!

 

But thank you very much for this, for sticking with it. You've helped me immensely.

 

Have a great weekend.

Hi @Budfudder,

 

Have you solved your problem?

 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.