How to show specific date based on slicer?

[YunJ]

Hi,

In my original raw data, it starts from 1/1/2019 to 9/15/2021.

I'm wondering whether I can achieve this performance:

If the slicer YearMonth = 2021/07, the bar chart will only show from 2021/01 to 2021/07.

Also if the slicer YearMonth = 2020/07, the bar chart will only show from 2020/01 to 2020/07.

One thing needed to notice is that, I already made a one to many relationship between date table and the fact table with "Date" column. (Because I have other charts in this dashboard).

Thanks for your time.

Yun

[v-chenwuz-msft]

Hi  YunJ ,

There are two tables.

Fact table and date table, if you have created a relationship between them, then copy the date table without no relationship.

Fact table :

Date table:

1 Create a measure

Measure =

var Selectdate=SELECTEDVALUE('Date table'[Date])

return

CALCULATE(SUM([Value]),

        FILTER('Fact Table','Fact Table'[Year/month].[Year]=YEAR(Selectdate)&&'Fact Table'[Year/month]<=Selectdate)

)

2 Create a chart and put field Year/month in the Axis(Under the drop-down menu, leave the date hierarchy unchecked, as shown in the picture.), Measure in the Values.

3 Create slicer with the Date table’s Date column(Similarly, do not select date hierarchy). Now you select a month and the chart will show the values before that month in the current year.

Best Regards

Community Support Team _ chenwu zhu

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

[YunJ]

hi, may I have a demo?

[v-chenwuz-msft]

hi, here the demo.

Best Regards

Community Support Team _ chenwu zhu

[amitchandak]

@YunJ , Are planning to select one period and want to show data more than that. Then you need an independent table

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI