Display N number of months based on Month and Year selection

[Anonymous]

Hi,

I have a simple measure 

TotalAccount = DISTINCTCOUNT(Accounts[AccountID])

I want to display this measure as a chart visual

When I select Month and Year the chart will be displayed like this

I would like the chart to display 6 months of data based on the month and year selection

Note:
1. I do not want to create another Date table but would like to use the existing Calendar table

2. I also do not want to use Relative Date Range slicer because the other measures and visuals need to display results based on Month and Year selection only and not date range selection

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Context affects measure, and you need a calendar table.

Refer the sample

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

Context affects measure, and you need a calendar table.

Refer the sample

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi Liang,

Thanks a lot, your technique works great!

However I had to make few changes to get this to work 

Sorry for the delayed response as I was testing this on a bigger scale 

Yes, I do have a Calander table but did not want to create a separate Calendar table for this purpose because other measures and visuals will be effected. I said that because I found a another way to  achieve this is by creating an additional Calendar table which also works but I wanted to have only one Calendar table in my model to keep all the other stuff intact

Here are the changes I had to do to get this work:

Based on your model, I had to bring in the "Year", "Month", "SortMonth" column to the Sales table from Date table

and updated Sales measure as

Sales last 6 months =
CALCULATE (
SUM ( Sales[Sales] ),
DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ),-6, MONTH )
)

[jeroenterheerdt]

So you want to override the behavior that the slicer provides? Why provide the slicer in the first place then?