Trailing 12 Average Balance (Measure based on another measure)

Helper III

4 years ago

I am facing bit different problem, kindly guide me on this

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 Fmonth= MAX('calendartable'[StartofMonth])

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

Return

CALCULATE(

COUNT('Data'[Categories]),

FILTER('Data','Data'[End Date] >=Fmonth && 'Data'[Start Date] <= Lmonth))

There is no relationship between calendar table and main table to get coorect solution for above point.

2. Now I want trailing 12 months in stacked bar chart with the counts of above measure for respective categories i.e.

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

12 Months Measure =

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

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

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

Return

CALCULATE(

COUNT('Data'[Categories]),

FILTER('Data','Data'[End Date] >= Fmonth && 'Data'[Start Date] <= Lmonth),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

Kindly suggest. Thanks in advance.

Forum Discussion

Trailing 12 Average Balance (Measure based on another measure)

Related content

12 months trailing measure

Measure

DAX Measure

Path Measure

% of total measure