Dynamic Display Last 12 Months in Visual According to Month Slicer

[udian]

Hi All,

In my report i have a month slicer that slices some of the visuals in the report. There are ohter column visuals that display measures values per month for different months (X axis) .

I want my column charts to display data only for the last 12 months prior to the month chosen in the month slicer.

Any ideas how to achive this?

Thank!

[Zunair]

Assuming you have a calendar datetable

* Make a column to count days in m query where [Date] is your calendar date.

* Days Filter=Number.From(DateTime.Date([Date]) - DateTime.Date(DateTime.LocalNow()))

On report page

* Filter visual by "Days Filter" with less 1 and greater than -356. Note that you can add same field multiple times.

* Then filter by top 12 months... done 😉

EDIT:
Here is a better one

* Make a column to count months where [Date] is your calendar date.

* Months Filter=(Int32.From(Date.Year(DateTime.Date([Date])) - Date.Year(DateTime.Date(DateTime.LocalNow()))) * 12) +
(Int32.From(Date.Month(DateTime.Date([Date])) - Date.Month(DateTime.Date(DateTime.LocalNow()))))

On report page

* Filter visual by "Months Filter" column greater than -12

[udian]

Anyone?

[v-micsh-msft]

Hi udian,

Currently I don’t think this could be done.

In order to have the column visual to dynamic display last 12 months value, we need the Axis to be set with the time range between the month selected and the month that is 12 month earlier. Based on the current version of Power BI Desktop, this is not allowed. The Axis well of the Column Chart Visual should be one of the column that exists in the dataset, or a measure. And a measure, based on what I know, can’t hold a time range.

The Filters under Column Chart only supports fixed parameters, if we would like to display last year sales, we could take a try to set a special filter under month:

By the way, if you would like to analyze the previous data, we could write measure to calculate that data  12 month ago based on the selected date, using the following formula:

sales_12Mbefore = calculate(

                                      sum('Salestable'[Sales]),

                                      PARALLELPERIOD('Salestable'[Date],-12,MONTH)

                                            )

Adding this under the value well and the proper date in Axis well, should be able to display sales 12 month ago.

Hope this is helpful.

Edited:

Please check and see if the reply from matemusic in the following thread would help:

http://community.powerbi.com/t5/Desktop/Show-last-13-months-based-on-user-single-slicer-selection/m-...

Regards

[Zunair]

Make a column to count days in m query where [Date] is your calendar date.
Days Filter=Number.From(DateTime.Date([Date]) - DateTime.Date(DateTime.LocalNow()))

On report page, filter visual by "Days Filter" with less 1 and greater than -356. Note that you can add same field multiple times.

Then filter by top 12 months... done 😉