Filtering Date Table by measure Previous 12 Month

[AnSchmi]

Dear BI Team,

my report contains visuals showing history of 5 year sales.
In one of the visuals I would like to show the monthly sales of the previous 12 month.

I can do it static by filtering the Current Month Offset in the visusal, however
is there a possibility to filter the date range by measure ?
In order to do so I need to figure out the selected Current Month Offset and selected Current Month Offset -12.

Calculate(
[Total Sales Value],
Filter(
'd TblCalendar',
'd TblCalendar'[CurMonthOffset] <= SelectedMonthOffset &&
'd TblCalendar'[CurMonthOffset] >= SelectedMonthOffset -12

)
)

My approach so far gives me always the Sales value of all transactions in fact table instead
of only the range of 12 month.

My files and static filter are looking as follows:

The data I would like to get by filtering dynamically =

Thank you for your help.

[amitchandak]

@AnSchmi , Try like

ou can use maxx(allselected(Table), Table[Date])  in place of today 

Date table and slicer on date table is assumed

rolling 12 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

or

rolling 12 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,filter('Date', 'Date'[Date]>= _min && 'Date'[Date]<= _max))

or

rolling 12 =
var _max = if(isfiltered('Date'),MAXX(allselected('Date'), 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,filter('Date', 'Date'[Date]>= _min && 'Date'[Date]<= _max))

[AnSchmi]

Dear yingyinr and amitchandak, 
the filtering is still not clear to me, sorry. 
I created a limited pbix file in order to explain what I want to achieve. 
With following measure I want to filter only 12 month based on the offset in calendar table. 

Measure Previous 12 Month Value =

VAR _MaxOffsetMonth =

MAX('d_Tbl Calendar'[CurMonthOffset])

VAR _MinOffsetMonth =

_MaxOffsetMonth - 12

Return

CALCULATE(

[Total Sales],

'd_Tbl Calendar'[CurMonthOffset] <= _MaxOffsetMonth,

'd_Tbl Calendar'[CurMonthOffset] >= _MinOffsetMonth

)

However, in the matrix there is showing all dates of calendar table (2017 - 2030)
I want to filter only range of current offset - 12 month like this 

How can I provide the pbix file if needed?
Thanks a lot. 

[v-yiruan-msft]

Hi @AnSchmi ,

You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[AnSchmi]

Dear Yingyinr, 
this is the link to the pbix file. 

https://fileshare.ssl.iscar.com/data/public/bc5768

Best Regards

Andreas

[AnSchmi]

Dear amitchandak, 
would you pls. have a look again on the definition of variable _min. 
var _min = date(Year(_max), month(_max) -12, Day(_max))+1,
BLANK())
The ,BLANK()) expression not functioning.
Without it I do get the same result as before.
Thanks a lot

Andreas 

[v-yiruan-msft]

Hi  @AnSchmi ,

You can refer the following links to get it:

Rolling 12 Months Visuals

Rolling 12 Months Sales = CALCULATE (

    SUM(‘Sales Fact'[Revenue]),

    DATESBETWEEN (

        ‘Date'[Date],

        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( ‘Date'[Date] ) ) ),

        LASTDATE ( ‘Date'[Date] )

    )

)

Total acumulado/Suma de balanceo Los últimos 12 meses

Rolling Sum Last 12 months =
CALCULATE (
    SUM ( 'Table'[YTD] ),
    ALL ( 'Table' ),
    DATESBETWEEN (
        'Calendar'[Date],
        DATEADD ( LASTDATE ( 'Calendar'[Date] ), -11, MONTH ),
        LASTDATE ( 'Calendar'[date] )
    )
)​

Rolling 12 Months DAX Measure in Power BI

How to create a measure for rolling 12 months invoiced sales

Best Regards