DAX Returning only One Value instead of all

[bw70316]

I have a budgetary report I have been working on and I have been working to get the Expenditure by Month by using the following equation:

Expensed Measure = SUM(Table1[Expensed])

Per Month total = CALCULATE([Expensed Measure],DATEADD(Table1[Exp Date],-1,MONTH))

Monthly Totals = [Expensed Measure]-[Per Month total]

This yields:



If change it to -2 I only get the correct data for September. How do I get ALL of the data? I have tried inputting an ALL funtion but can't figure it out. Also, I was able to get this to work with raw data, which means there could be something corrupted in my PBI report file?

[v-chuncz-msft]

@bw70316,

Try using PREVIOUSMONTH instead of DATEADD and take a look at https://www.sqlbi.com/articles/filter-arguments-in-calculate/.

[dedelman_clng]

DATEADD(Table1[Exp Date],-1,MONTH) against Aug 31, 2018 literally brings back July 31, 2018, which you don't have in your dataset.

Create a calendar table ( DateTab = CALENDARAUTO() ) if you don't have one, and create a 1-to-many relationship between it and Exp Date.

Then use PREVIOUSMONTH()

Per Month total = CALCULATE([Expensed Measure], PREVIOUSMONTH(DateTab[Date]) )

If that doesn't work, please provide a sample of your raw data.

Hope this helps

David