Forum Discussion
Helper IIITrailing 12 months bar chart based on slicer
Helper IIII tried this but I have one more challenge along this , need to apply two filters
1. Is trailing 12 months
2. If user selects Jan'22 in calendar end date then will get counts if start date <= user select date and end date >= user select date
Based on that I have created this measure but it's not working
ALL('calendartable'[EO Month]), {this is for 1st point}
FILTER('calendartable','calendartable'[EO Month]>= PP)) {this is for 1st point}
Community ChampionSK87
I don't fully understand your filter requirements. But in general you need to filter the DATESINPERIOD table. In the same file you can find a measure that filters the days in the pervious months based on the selected dates. For example if you select 15th of December 2021 then all the previous months will be filtered to show the data upto the 15th of each month. Not sure if that helps you.
- SK87
There are two filter requirements:
1. Suppose if user select Dec'21 in slicer, then the data will filter out as described below:
In start date column data would be selected less than equal to user selection that is <=Dec'21 and in end date selection would be greater than equal to user selection >-Dec'21.
which I can get by below measure:VAR Smonth= MAX('calendartable'[StartofMonth])VAR Smonth1= MAX('calendartable'[EO Month])ReturnCALCULATE(COUNT('Data'[Categories]),FILTER('Data','Data'[End Date] >= Smonth && 'Data'[Start Date] <= Smonth1))2. Now once above is working I want the counts to be shown in trailing 12 months i.e. if user select Dec'2021 - bars should be trailing 12 months from Jan'21-Dec'21 but counts should be same as point 1.Hope this will help you.