Filter visual date axis range based on selected date

[Bthurlings]

Hi,

I'm struggling with the following: I have a visual in my report with DATE on my axis and production efficiencies in the values. I want the date axis max month to display data for the beginning of the year until the month selected in my slicer.

So when I select "September" in my slicer, the bar chart should only show data from January till December. When I select "July", it should only show January till July.

At the moment I can only control the effect of the slicer setting for the visual to either filter down to the selected month, or disable the filter settings but then filtering the date will not affect the visual at all.

What's the best way to approach this?

[Anonymous]

Hello,

@Greg_Deckler 

I know this is an old post but I am struggling with a similar situation and hoping that you would have a solution. I have a calender table connected to a fact table with a date key. I have multiple visuals on my report page all of which use the calender table slicer date selection. There is one visual that needs to show last 3 months of data based the slicer selection. I have written a datediff measure which works fine on this visual if I disconnect the calender table from the fact table. However, I need the relationship between the two tables for my other visuals. 

From the answer above in this post, it seems the USERELATIONSHIP function can help but I have no clue on how to implement it. I would really appreciate any guidance on it.

Please note that I cannot use measures like Previoumonth value etc to get around this problem since I this visual is a stacked bar chart that cannot handle multiple measures.

Thanks a lot,

Leena

[Greg_Deckler]

@Anonymous Well, the way that I would do that would be to probably use something like:

Measure =
  VAR __Date = MAX('Slicer'[Date])
  VAR __3MonthsAgo = EOMONTH(__Date,-3)
RETURN
  SUMX(FILTER(ALL('Table'),[Date]>=__3MonthsAgo && [Date]<=__Date),[Column])

[Anonymous]

Sample File@Greg_Deckler 

Thank you for your prompt reply. Unfortunately I was not able to make that work. I am attaching a simplified version of my pbix file. It should clarify what I am looking for. I have been struggling with it for a while now. Will appreciate your help,

Not sure if the attached file with go through so inserting a screenshot of my report

[Bthurlings]

SQLBI recently posted a nice tutorial on something similar which might prove useful:

https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/

(this works with any number of periods btw)

[Anonymous]

Hi @Bthurlings , @Greg_Deckler ,

I checked out SQL BI's article above and tried to implement that as well but it still did not work for me. Copying the link to the sample file for SQL BI's solution below. I have written to them too but would really appreciate if you could look into it as well.Sample File 

[v-piga-msft · Accepted solution]

Hi @Bthurlings,

From my understand, you want to show the date range in axis from the beginning of the year to the date month you select in date slicer?

---

 So when I select "September" in my slicer, the bar chart should only show data from January till December. When I select "July", it should only show January till July.

However, I have a little confused that why the bar chart should show data January till December when you select "September" in slicer? Do you have a written mistake or that is what you want?

If you want the axis to display the data for the beginning of the year until the month selected in the slicer.

You could create another date table with Calendar fucntion and do not create relationship between the two tables.

Then create the measure with the formula below.

Measure =
VAR a =
    MONTH ( MAX ( 'Calendar'[Date] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            'Table',
            'Table'[Date] >= STARTOFYEAR ( 'Calendar'[Date] )
                && 'Table'[Date] <= MAX ( 'Calendar'[Date] )
                && MONTH ( 'Table'[Date] ) <= a
        )
    )

Here is the output.

For more details, you could refer to my attachment.

Best Regards,
Cherry

[Anonymous]

Can you also help me?

I need to have a moving months on x-axis based on the slicer selection. x-axis needs to show 6 months depending on slicer:

Ex: if selected month on slicer is Dec'19; x-axis to show Jul'19 to Dec'19. On the other hand, if selected month on slicer is Sep'19 then x-axis to show Apr'19 to Sep'19.

Thanks in advance!

[Bthurlings]

Thanks! Indeed I made a typo in my initial problem statement. The solution you suggested works fine.

To filter other visuals in my dashboard using your solution, which do require an active relation with the date table, I found out that using the USERELATIONSHIP function does that trick.