Forum Discussion
Formula Returning Blank
I'm simply trying to capture the sum of revenue for the previous quarter. Is there any reason why this formula isn't returning anything at all?:
Hi Anonymous,
As Anonymous mentioned, bringing in Year/ Date field to your visual should fix this
Regards,
Thejeswar
7 Replies
To use the built-in Time Intelligence functions, you need to have a formal Calendar table that has been "Marked as Date Table"
This table needs to have ALL of the dates in a year, even weekends and holidays.
A relationship then needs to be created between your CalendarTable[DateColumn] and your 'M3 Billing'[Invoice Date]
SQLBI has a great Date table in their template file for free download. Check out this article for more information.
Note that this table is likely overkill, but it has been designed to work in nearly EVERY use case.
Anonymous, it still needs to be done in DAX, but you need to have your model properly set up in order for those DAX functions to work as expected.
There are a lot of good articles out there on Time Intelligence using DAX (and how to properly set up your data model for it). I recommend looking at this link from Matt Allington
Hi Anonymous,
The Measure is to be written as follows
Previous Quarter Sales = CALCULATE(SUM(Sheet1[Sales Amount]), PREVIOUSQUARTER('Date'[Date]))Here Sheet1 is your actual data and Date is a calendar table that has all the dates in the year/Quarter
These tables should be related by Date
Data screenshot:
Here 2018 has Q1 data and 2017 has Q4 data
Regards,
Thejeswar
Thejeswar There's gotta be something I'm missing. I've tried that and the formula just wants to be blank for some reason: