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
Anonymous
Not applicable

Analysing data that is not in selection of timeline

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.

7 REPLIES 7
v-huizhn-msft
Employee
Employee

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
Not applicable

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!

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
Not applicable

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.

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 ) - 2MONTH ( MinSelDate )DAY ( MinSelDate ) )
RETURN
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] >= MinSelDateLY
                && 'Calendar'[Date] <= MaxSelDateLY
        )
    )

Best Regards,
Angelia

bdymit
Resolver II
Resolver II


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 ) - 1MONTH ( MaxSelDate )DAY ( MaxSelDate ) )
VAR MinSelDateLY =
    DATE ( YEAR ( MinSelDate ) - 1MONTH ( 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
Not applicable

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.

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.