Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Display 6 months on the X axis

Hi Champs, I have a situation where I am displaying Totals by months on  a clustured bar chart where months are on X axis and Totals is being displayed on Y axis. And, I'll be selecting a month fro...
Show and Tell
  • danextian's avatar
    danextian
    1 year ago

    Hi Anonymous 

     

    You will need to use a disconnected table for the slicer as using columns from a related one will filter the visible rows based on what's been selected. For example, if you select Jan-25 you will only see the Jan-25 dates.

     

    Agg In the last 12 Months = 
CALCULATE (
    [Sum of Value],
    KEEPFILTERS (
        DATESINPERIOD (
            DatesTable[Date],
            MAX ( DisconnectedDatesTable[Date] ),
            - 12,
            MONTH
        )
    )
)

     

    The third column in the image below uses the formula above and shows only the last 12 months based on the slicer selection.  If this what you're looking for, please see the attached pbix.

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Great danextian 
Works well. Just one problem I see is the slicer displays all the months irrespective to the Fact table. Like if there is no data for April, the slicer should not display those months.

Can we modify it further?

danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

The first measure is not filtered by the disconnected table column so all rows still show irrespective of the month selected. If removed, all columns not within the 12, or 6 in your case, won't be visible.