Forum Discussion

SK87's avatar
SK87
Helper III
4 years ago

Trailing 12 months bar chart based on slicer

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.


 

 

12 Replies

  • SK87's avatar
    SK87
    Helper III
    4 years ago

    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's avatar
      tamerj1
      Community Champion
      4 years ago

      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's avatar
        SK87
        Helper III
        4 years ago

        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.