How to make a bar chart based to time filter?

[YunJ]

hi,

I'm wondering if I select filter 2020.04, can it show bar chart from 2020.01 to 2020.04?

It would be great if you could provide a pbix sample file. Thanks a lot. Yun

[V-lianl-msft]

Hi @YunJ ,

First you need to create an unrelated calendar table as a slicer.

Then create the following measure and apply it to the visual level filter.

Measure =
IF (
    YEAR ( MAX ( 'Table'[date] ) ) = SELECTEDVALUE ( 'Table 2'[year] )
        && MONTH ( MAX ( 'Table'[date] ) ) <= SELECTEDVALUE ( 'Table 2'[month] )
        && MONTH ( MAX ( 'Table'[date] ) ) >= 1,
    1
)

Sample .pbix

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

[YunJ]

Hi, could you please save to 2019.11 version or before?

Thanks

Yun

[V-lianl-msft · Accepted solution]

Hi @YunJ ,

Sample .pbix  has been updated.

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

[nandukrishnavs]

@YunJ 

Sales

Date	Sales
01 January 2020	350
01 February 2020	120
01 March 2020	456
01 April 2020	246
01 May 2020	100
01 June 2020	400
01 July 2020	500
01 August 2020	120
01 September 2020	205
01 October 2020	456
01 November 2020	246
01 December 2020	100

DateTable

Date

01 January 2020

01 February 2020

01 March 2020

01 April 2020

01 May 2020

01 June 2020

01 July 2020

01 August 2020

01 September 2020

01 October 2020

01 November 2020

01 December 2020

Do not create any relationship.

create a DAX measure

Date Filter = 
var d=MONTH(SELECTEDVALUE(DateTable[Date]))
var c=MONTH(SELECTEDVALUE(Sales[Date]))
var result= IF(d<=c,0,1)
return result

Create slicer from DateTable

create a bar chart using the Sales Table.

Then drag DateFilter measure into the visual level filter. Value is 1

Try this.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[YunJ]

Thanks a lot!

Could you please send me the pbix file?

Also, why we cannot set the relationship? Because sometimes not every month has value, at this time I also need its month on the axis (although there is no value).

Thanks!

Yun

[nandukrishnavs]

Sorry I didn't save the file. Please follow the above steps. I'm sure you can replicate the same.

If you enable the relationship between the above mentioned tables, automatically filter the bar chart based on slicer.

The idea here is, you have to write the logic in the DateFilter measure. I have provided you one example. Based on your real scenario, you can tweak its logic.