Issue rolling 12 months ignore date

[Anonymous]

Hi Guys, my current expression for calculate rolling 12 months 2016: Rolling 12 Month Sales 2016 = CALCULATE( [TotalSales2016], FILTER(ALLSELECTED('Sales invoice line'), 'Sales invoice line'[Invoice date] <= MAX('Sales invoice line'[Invoice date]))) In the following image you can see that when a date start with year 2017 the total amount of rollign 12 month sales 2016 of 55.456.109 appears. I would like that this number must not shown. Any ideas how i can change the measure? Thanks for helping out.

[v-yuta-msft]

@Anonymous,

To be general, modify your measure like pattern below and check if the issue persists. If still can't resolve, please share some raw data or your original table, make sure mask your sensitive data.

Rolling 12 Month Sales 2016 =
CALCULATE (
    [TotalSales2016],
    FILTER (
        ALLSELECTED ( 'Sales invoice line' ),
        'Sales invoice line'[Invoice date] = MAX ( 'Sales invoice line'[Invoice date] )
            && NOT ( ISBLANK ( 'Sales invoice line'[Invoice date] ) )
    )
)

Regards,

Jimmy Tao

[Anonymous]

@v-yuta-msft,

Thank you for quick response.

Unfortunatly , your measure is not solving my problem. The query now just summing the total sales of 2016. 

fyi: the measure of
TotalSales2016 is = 

 CALCULATE([TotalSales]; 'Dim Calender Sales Invoice'[Year] = 2016)

the relationship between the table of Sales Invoice Line and Dim Calender Sales Invoice is SalesInvoiceLine.InvoiceDAte (*:1) DimCalenderSalesInvoice.FullAlternateDateKey....

[v-yuta-msft · Accepted solution]

@Anonymous,

According to your description, it seems the slicer is based on the column in the fact table 'Sales Invoice Line', and you want to filter the dimensional table on the '1' side, right? Suppose the slicer is based on the year column in the fact table. Try DAX below:

Rolling 12 Month Sales 2016 =
CALCULATE (
 [TotalSales2016],
 FILTER (
 'Dim Calender Sales Invoice',
 'Sales invoice line'[Invoice date] = MAX ( 'Sales invoice line'[Invoice date] )
 && 'Dim Calender Sales Invoice'[Year] IN VALUES ( 'Sales invoice line'[Year] )
 )
)

If still can't meet your requirement, please provide some sample data and clarify more details about your expected result.

Regards,

Jimmy Tao