Trailing 12 months bar chart based on slicer

[SK87]

Hi All

I need to create a bar chart where I have trailing 12 months bars along with counts of categories of top 3+1(other).

1. I have created a calendar table and another column for End of month to use that in slicer (Month,Year). [calendar Table]

2. Created a table for unique categories and then calculated the counts.

Category = DISTINCT(

    FILTER(

        VALUES('Data'[Categories]),

        'Data'[Categories] <> "Null"))

*****************************************        

CALCULATE (

    COUNT('Data'[Categories]),

                  FILTER(  'Data', 'Data'[Categories]='Category'[Categories])

)

3. Rank based on counts

RANKX('Category', 'Category'[Count of Categories])
4. Categories displayed:

IF('Category'[Rank]>3, "Other", 'Category'[Categories])
5. Now calculating a data where I can get the Total counts - 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 and data bars should be trailing 12 months

12 Months Measure =

VAR Smonth= MAX('calendartable'[StartofMonth])

VAR Smonth1= MAX('calendartable'[EO Month])

VAR PP= DATE(YEAR(Smonth), MONTH(Smonth)-12,DAY(Smonth))

Return

CALCULATE(

    COUNT('Data'[Categories]),

    FILTER('Data','Data'[End Date] >= Smonth && 'Data'[Start Date] <= Smonth1),ALL('calendartable'[EO Month]),FILTER('calendartable','calendartable'[EO Month]>= PP))

Now when I created the Chart :

X-axis: Calendartable[EOMONTH]

Y-axis: 12 Months Measure

Legend: Categories displayed

Filter: Rank

counts are coming fine but Trailing 12 months not working

@tamerj1 @amitchandak @SpartaBI  it would be great if anyof you could suggest a way out.

[SK87]

@tamerj1 

I 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

12 Months Measure =

VAR Smonth= MAX('calendartable'[StartofMonth])

VAR Smonth1= MAX('calendartable'[EO Month])

VAR PP=

DATESINPERIOD('Calendartable'[Date],  Smonth1, -12, MONTH)

Return

CALCULATE(

    COUNT('Data'[Categories]),

    FILTER('Data','Data'[End Date] >= Smonth && 'Data'[Start Date] <= Smonth1),      {this is for 2nd point}
    ALL('calendartable'[EO Month]),                                                                                 {this is for 1st point}
    FILTER('calendartable','calendartable'[EO Month]>= PP))                                          {this is for 1st point}

Italic one is not giving me correct solution for trailing along with first filter

[tamerj1]

@SK87 
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]

@tamerj1 

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])

Return

CALCULATE(

    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.

[tamerj1]

@SK87 

which column is connected to CalendarTable[Date]?

[SK87]

@tamerj1 

1. Many to one actie relationship  Data[SELECT MONTH] - Calendartable[DATE]

2. Many to one Inactive relationship Calendartable[Date] - Previous persiod[selectmonth]

Previous period is copy of calendar table

[tamerj1]

@SK87 

Please try

=
VAR Smonth =
    MAX ( 'calendartable'[StartofMonth] )
VAR Smonth1 =
    MAX ( 'calendartable'[EO Month] )
RETURN
    CALCULATE (
        COUNT ( 'Data'[Categories] ),
        'Data'[End Date] >= Smonth,
        'Data'[Start Date] <= Smonth1,
        CROSSFILTER ( Data[SELECT MONTH], Calendartable[DATE], NONE )
    )

[SK87]

@tamerj1 Thanks for the solution.

But how trailing 12 months will function, in above measure related to trailing 12 months nothing is specifed. 

[tamerj1]

@SK87 

Probably in this case a previous date table shall not be required. I'll look into it tomorrow. 

[SK87]

@tamerj1 

@amitchandak 

@SpartaBI 

@daXtreme 

@johnt75 

Guys anyone who could help me in solving this problem, it would be great help.

Thanks in advance.

[SK87]

@tamerj1 Waiting for your response on this.

[SK87]

@tamerj1 hope you understood the problem

[tamerj1]

Hi @SK87 

please try the method I proposed in in this post 

https://community.powerbi.com/t5/Desktop/Display-data-for-previous-6-months/m-p/2719708#M949700