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

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

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

    tamerj1 Thanks for the solution.

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

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      SK87 

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

      • SK87's avatar
        SK87
        Helper III
        4 years ago

        tamerj1 Waiting for your response on this.