Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 beginnin...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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.

     

    For more details, you could refer to my attachment.

     

    Best Regards,
    Cherry

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Greg_Deckler
Community Champion
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    Sample FileGreg_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