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
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.

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

        Hi thliberato,

         

        Have you checked my sample shared above? It should work as expected.:smileyhappy:

         

        According to your description, it seems that you are using drill down on the chart, right? That's why the chart only shows data for a specific year from Jan to your selected month. So please recheck my sample and use a YearMonth column as I used in the sample, and don't use drill down to show year with month, then it should work.

         

        Regards