Relate Data to last Month

[FlashFire]

I've been trying to create a formula to relate data to the last month in DAX. Nothing I've found or created has worked.

What I've Tried:

CALCULATE(
SUM('Data Warehouse'[EXT AMOUNT]),
--VALUES('Data Warehouse'[EXT AMOUNT]),
----------
--PREVIOUSMONTH(ALL('Dates'[Dates]))
PARALLELPERIOD(ALL('Dates'[Dates]),-1,MONTH)
--DATEADD('Data Warehouse'[Sale.Invoice Date],-1,DAY)
--DATEADD(DATESMTD('Data Warehouse'[Sale.Invoice Date]),-1,MONTH)
--FILTER(
-- ALL('Dates'[Dates]),
-- EARLIER('Dates'[Index]) = 'Dates'[Index]-30
--)
)

I've tried all of these. The only one that doesn't return blank data is PARALLELPERIOD, and it returns the same data as EXT AMOUNT.

 

[akice]

Assuming you have a separate Calendar table marked as Date Table, Calendar table linked to Data Warehouse via Dates[Date] field, and power pivot is setup to filter to current period, then should be just:

[COLOR=#333333]CALCULATE( [/COLOR][COLOR=#333333]SUM('Data Warehouse'[EXT AMOUNT]),[/COLOR][COLOR=#333333]PREVIOUSMONTH( 'Dates'[Dates] ) )[/COLOR]

 

[FlashFire]

Assuming you have a separate Calendar table marked as Date Table, Calendar table linked to Data Warehouse via Dates[Date] field, and power pivot is setup to filter to current period, then should be just:

[COLOR=#333333]CALCULATE( [/COLOR][COLOR=#333333]SUM('Data Warehouse'[EXT AMOUNT]),[/COLOR][COLOR=#333333]PREVIOUSMONTH( 'Dates'[Dates] ) )[/COLOR]

I'm using Power BI, not Power Pivot. I've created a relation between Data Warehouse and Dates already.

As far as I know, there isn't a setting to make a table a Date Table.

And the code provided does nothing. No error, No data displayed.

 

[akice]

Power BI doesn't have a Mark as Date and if you have linked the Date column from Calendar to Data Warehouse you shouldn't need Mark as Date anyway. Does your Calendar table include contiguous dates for all dates you want to report on (current and previous months)? what are you putting on rows/columns? Other filters?

Formula provided should work.

 

[FlashFire]

Yes, the Calendar table does have contiguous dates. The columns in the table are Index, Day of Week, and Month. The only relation between Data warehouse and Dates is Invoice Date and Dates.

I made the Dates table in excel, and removed the single Null value. So it should be correct.

 

[akice]

What happens if you put "Months" on the rows and remove the "Index" and "Day of Week"? Does measure work?