Forum Discussion

thliberato's avatar
thliberato
Helper I
9 years ago

Dynamic Graph month

I have a calendar filter to choose a month. I have a bar Graph with quantity tickets x System Name X Month. I need show in this graph the last 12 months considering the month selected in filter.  ...
Need Help
Show and Tell
thliberato's avatar
thliberato
Helper I
9 years ago

austinsense, Good Morning. 

 

It did not work out as I expected.

 

This is the Example.
I Have a Filter Data to choose one month. When I choose the month, my Graph needs show me only last 12 month, including the month selected.

 

Once again, thank you.

austinsense's avatar
austinsense
Impactful Individual
9 years ago

Sorry this is my fault - I answered the wrong question :/

 

You're looking for a dynamic filter on the date axis - I have to get to a meeting but I can take a look at this later.

  • thliberato's avatar
    thliberato
    Helper I
    9 years ago

    Good afternoon austinsense

     

    Merry Christmas. Did you see something about it?

     

    Thanks a lot.

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi thliberato,

      I Have a Filter Data to choose one month. When I choose the month, my Graph needs show me only last 12 month, including the month selected.

      According to your description, you should be able to follow steps below get the expected result.

       

      1. You need to add a new date table to your model, and make sure there is no any relationships between the new date table and other tables.

      2. Use the formula similar like below to add calculate column to both the new date table and your calendar table.

      DateInt = YEAR ( NewDateTable[Date] ) * 12 + MONTH ( NewDateTable[Date] )

      3. Use the formula similar like below to create the measure that you want to show in the visual.

      Last12MontnSales = 
IF (
    MAX(DimDate[DateInt])<=MAX(NewDateTable[DateInt]) && MAX(DimDate[DateInt])>MAX(NewDateTable[DateInt])-12,
    SUM ( FactInternetSales[SalesAmount] ),
    BLANK()
)

      4. Use the date column of the new date table as the Slicer, and show the measure with the date column of your calendar table on the report.

       

       

      Here is the sample pbix file for your reference.:smileyhappy:

       

      Regards

      • thliberato's avatar
        thliberato
        Helper I
        9 years ago

        v-ljerr-msft, austinsense, Framet,

         

        It did not work as expected.

         

        If I select month May, the chart only shows me 5 columns (May, Apr, Mar, Feb and Jan).

        If I select month Apr, the chart only shows me 4 columns (Apr, Mar, Feb and Jan).

        If I select month Apr, the chart only shows me 3 columns (Mar, Feb and Jan).

        If I select month Apr, the chart only shows me 2 columns (Feb and Jan).

        If I select month Apr, the chart only shows me 1 column (Jan).

         

        I need the graph that always display 12 months, always ending in the selected month in the filter.