Calculate total in last 12 month from 3 tables

[Anonymous]

What's wrong with below DAX to calculate the total for last 12 month from 3 tables.  i'm using calendar[date] connected the dates from 3 different tables.

Last12Month = calculate(('Data-ANZ DD'[TotANZDD]+'Data-Westpac CC'[TotCC]+'Data-Westpac DD'[TotWDD]),'Calendar'[Date]>=DATEADD(LASTDATE('Calendar'[Date].[Month]),-12,MONTH))

[mattbrice]

Looking at your model there is no need to combine the 3 tables together.  You just need to create a measure to sum the relevant column in each table (which the three you list may already be?  in Dax the formatting guideline is to preface column names with the table name, and for measures just use the measure name. ) .  And the dot notion your use here "LASTDATE('Calendar'[Date].[Month])" isn't allowed in Dax (afaik anyway).

This formula should work:

Last12Month =
CALCULATE (
    [TotANZDD] + [TotCC] + [TotWDD],
    DATESBETWEEN (
        Calendar[Date],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[Date] ) ) ),
        LASTDATE ( Calendar[Date] )
    )
)

[Anonymous]

@mattbrice your fomular works but gave me the total for year 2017 (still very useful), but not the runing "last 12 month"?  thanks. 

[v-huizhn-msft]

Hi @Anonymous,

Have you resolved your issue? If it does, please mark the helpful reply as answer.  And feel free to ask any issue if you haven't resolve your problem.

Best Regards,
Angelia

[mattbrice]

The formula bases the date range on the last date visible in the current filter context.  You need to slice the data to final month or date and the formula should give you the previous year results you want.

[Anonymous]

@mattbrice sorry i'm not quite get it.  i'm using calendar table date, which is Calendar = calendar("2015-1-1","2017-12-31")

below calculation gave me the total for "2017" but i need the running total for "last 12 month".  

CurrentYear = calculate([TotalAmount],DATESBETWEEN('Calendar'[Date],NEXTDAY((SAMEPERIODLASTYEAR(LASTDATE('Calendar'[Date])))),LASTDATE('Calendar'[Date])))

[Phil_Seamark]

HI @Anonymous,

Were your three tables similar enough to generate a single table?   This will result in fewer/simpler measures and a faster model.

Cheers,

Phil

[Anonymous]

no, they are different, but i do have a sum "Measure" which is the sum "amount" from each table, then sum those 3 "sums" together. 

[v-huizhn-msft]

Hi @Anonymous,

As the @mattbrice posted, please create a slicer including 'Calendar'[Date]. When you select the 2016/12/31, it will return the total sum 2016.

Best Regards,
Angelia

[mattbrice]

Add a filter/slicer for the year and month you want to be the last, then formula should work.

[Phil_Seamark]

Hi @Anonymous,

Would you consider combining the three tables into 1 (using either DAX or the Query Editor)?