Analysing data that is not in selection of timeline

[Anonymous]

Hi everyone!

I'm needing some help. I trying to make a report that has a timline slicer. The dates that I use for the timeline, is a table that has filtered the main date table so it displays only the last 2 years.

So, in my example, I have a range of timeline from jun-2015 to jun-2017. One of the measures that I had created is the sales of same period last year. If I select the first item of the timeline slicer, jun-2015, these measure does not work. I already know that it is because is trying to get data of a date that is not part of the timline.

My question is, how can I show that data without adding more items to the timeline? If I select jun-2015, I want to see the sales of jun-2015 in the column that has the mesaure of sales of same period last year.

Thanks in advance.

[v-huizhn-msft]

Hi @Anonymous,

Have you resolved your issue? Please use the formula @bdymit posted. Please don't free to ask if you have other issue.

Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft.

Unfortunately that solution did not solved the issue.

It's ok anyway, I just accepted the fact that calcuations must use time range displayed in time bar selector.

Have a great day!

[v-huizhn-msft]

Hi @Anonymous,

I am still confusing, what;s your requirement? When you select jun-2015, which period value do you want? 

>>It's ok anyway, I just accepted the fact that calcuations must use time range displayed in time bar selector.

You still have the issue or not? Please share more details for further analysis.

Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft, how are you?

In my first description I made a mistake saying what I need to see when I select jun-2015.

What I would want, is that when jun-2015 is selected, wich is the first element of the timeline, the column that shows sales from same period previous year shows the sales of jun-2014.

I still haven't find a solution for this. What I accepted, as I mentioned in previous replies, is that I just accepted that this will always happen. In fact, in my model I have sales since jan-2007. So, if I select jan-2007, it will correctly not display any data in this column of previous sales. But, since a timeline from 2007 to 2017 is way to long, and I don't really want that much of analysis in all reports, in specific reports I made a custom timeline only with the last 2 years. In this last scenario, the table object that displays sales, I wanted to see sales of 3 years since the column of previous year sales is still there.

Hope I'm clear.

Have a great day.

[v-huizhn-msft]

Hi @Anonymous,

>>I wanted to see sales of 3 years since the column of previous year sales is still there.

You can use the similar formula like the following. Just adjust the formula based on @bdymit posted.
=
VAR MaxSelDate =
    MAX ( 'Calendar'[Date] )
VAR MinSelDate =
    MIN ( 'Calendar'[Date] )
VAR MaxSelDateLY =
    DATE ( YEAR ( MaxSelDate ) , MONTH ( MaxSelDate ), DAY ( MaxSelDate ) )
VAR MinSelDateLY =
    DATE ( YEAR ( MinSelDate ) - 2, MONTH ( MinSelDate ), DAY ( MinSelDate ) )
RETURN
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] >= MinSelDateLY
                && 'Calendar'[Date] <= MaxSelDateLY
        )
    )

Best Regards,
Angelia

[bdymit]

---

My question is, how can I show that data without adding more items to the timeline? If I select jun-2015, I want to see the sales of jun-2015 in the column that has the mesaure of sales of same period last year.

---

Did you mean "If I select jun-2015, I want to see the sales of jun-2014 in the column that has the mesaure of sales of same period last year."

Perhaps you could use variables to get min and max dates.

something like:

=
VAR MaxSelDate =
    MAX ( 'Calendar'[Date] )
VAR MinSelDate =
    MIN ( 'Calendar'[Date] )
VAR MaxSelDateLY =
    DATE ( YEAR ( MaxSelDate ) - 1, MONTH ( MaxSelDate ), DAY ( MaxSelDate ) )
VAR MinSelDateLY =
    DATE ( YEAR ( MinSelDate ) - 1, MONTH ( MinSelDate ), DAY ( MinSelDate ) )
RETURN
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] >= MinSelDateLY
                && 'Calendar'[Date] <= MaxSelDateLY
        )
    )

There is probably a more elegant solution, but I think that should work. Variables are evaluated in the context of their definition, which is, in this case, your original filter context.

Let me know if it works!

[Anonymous]

Hi @bdymit, thanks for your time and contributing.

This did not work for me. I've already tried something like that before, and I didn't get the expected result.

Thanks anyway!

Have a great day.