Running Total of 12 previous months excluding current month

[Shay A]

Hi,

I am trying to build a DAX expression to help me check, for each of the following months- 10/2017 through 09/2018 if there was sales ofat least one USD in the previos 12 months. For example, for 10/2017, the expression checks month 10/2016 till 09/2017. Also, the the formula shold check if there are sales on the month evaluated (10/2017 in this example).

TY!

 

[Chihiro]

As always, it would help us in helping you, if you upload sample workbook with data and manually created desired result.

 

[GraH - Guido]

First of all, do you have a calendar table in your data model, and does it contain a column with MonthRef like mm/yyyy-format that allows you to link the sales data with the calendar?
If so you can more easily use a real date filter on you sales table in the required measurement.

 

[Shay A]

I have only one table that I added to the Data Modal, and yes, it has a date column.

Thanks

 

[Chihiro]

Hmm, without sample hard to give you exact answer.

Depending on what date ranges are present in your data set, calculation changes a bit.

When you have date dimension table (DimDate), you could use following construct.

RSUM12M := CALCULATE (
    [Value],
    DATESBETWEEN (
        DimDate[Date],
        SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( DimDate[Date] ) ) ),
        LASTDATE ( DimDate[Date] )
    )
)

Full explanation of construct can be found in link below.
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/