Rolling 12 month value retaining all user slicer selections

[Telstar]

Hi

Hoping someone can assist in my struggle

I have 
Main table - facts and $ amounts(15 cols in total) - one date field: first of month

dimDate - calendar table - joined to main table on date>first of month

Currency table - joined to main table on currency column

Account table - hierarchy of accounts - joined to main table on account column

Business units table - hierarchy of BU - joined to main to on BU column

Amount type - list of amount type - budget, actual, prior year, working - joined to main on type columns; one or more types be selected.

UI shows slicers for currency,account,BU,Type and Month&year(from dimDate table) - user will select whatever combo they need.

Requirement is to show a rolling 12 month balance for the selections made in a clustered bar chart

The rolling 12 months are from the user select month&year backwards, summing the previous 12 months each time e.g

User selects Apr 2020 - graph will dispaly Apr 2020 - May2019 (right to left)

Apr 2020 = Sum(apr2020:May2019)
Mar2020 = Sum(mar2020:Apr2019)

May2019 = Sum(may2019:Apr2018)
The display is also the 12 months back from the user selection.

The issue I am having is that my measures(s) are aggregating the amount if two or more 'Amount Type' are selected

R12 measure

     R12_MTD =
          CALCULATE (
          SUM ( Main[Amount] ),
          ALL(dimDate),

          filter(ALLSELECTED(Main),Main[YearMonthNo]<=Max(Main[YearMonthNo]))
           )

 R12 display measure

      R12 (last 12 months) =
           CALCULATE (
           [R12_MTD],
           DATESINPERIOD ( 'dimDate'[Date], MAX ( 'dimDate'[Date] ), -12 MONTH ))

When the user makes a single selection for amount type e.g. Actual the bar chart displays as expected, all user selections applied.

If the user selects say 'Actual' and 'Budget' - two bars display (bud and act) but the values are identical and are the aggregation of budget + actual

So instead of showing Actual =50 and budget= 100 UI display is two identical bars of 150!!

Hope this is clear

Any help greatly apprecaited

Cheers

Telstar

[amitchandak]

@Telstar , You can try like this using date calendar

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(Sales[Sales Date]),-12,MONTH))  
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],startofmonth(Sales[Sales Date]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Telstar]

thanks for the response @amitchandak 

I have used a calendar table and it is marked as a such and links to my my main table on date.

Using any of your suggested DAX (in my scenario) doesnt give the desired ouput of a rolling total

I get the separated bars with amounts that are not identical but it is not actually performing the sum for 12 months I just get the monthly balance

Cheers

Terry