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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Filter visual date axis range based on selected date

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.

 

Image.PNG

 

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?

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

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.

Capture.PNG

 

For more details, you could refer to my attachment.

 

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

8 REPLIES 8
Anonymous
Not applicable

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

@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])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

Monica79_0-1623344772792.png

 

Anonymous
Not applicable

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

Hi @Anonymous , @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
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

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.

Capture.PNG

 

For more details, you could refer to my attachment.

 

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

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!

Anonymous
Not applicable

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors