Showing Monthly, Yearly Sales in the same report using Time Series Functions

Question

Hi,

My Automotive client has a requirement to create a sales analysis report that comprises the below columns:

* Car Model 
* Current Month Sales 
* Previous Month Sales 
* Current Year Sales 
* Previous Year Sales

Report Requirement Description:

User would be selecting Date Prompt, as per the Date Prompt selection the report should display the output.

Work done:

My client has requested to avoid using Union Reports for this requirement. Hence I created a Dimensional Hierarchy on Calendar (Date) Dimension and created the following calculated measures in the Logical Fact Table in the RPD: Current Month Sales, Previous Month Sales, Current Year Sales, Previous Year Sales using the Time Series functions 'ToDate' & 'Ago'.

The Outcome:

When I pull EITHER Car Model with Current Month Sales & Previous Month Sales OR Car Model with Current Year Sales & Previous Year Sales columns into the report Reports are working as expected but whenever I pull Car Model, Current Month Sales, Previous Month Sales, Current Year Sales & Previous Year Sales columns all together -- the report shows the data at Monthly level.

Am I missing something here? Can the above scenario be achieved using Time Series functions?

If not Time Series, what might be the other options for me?

Thanks!

Robert Angel · Answer

In terms of other options you could also use the function 'filter' to achieve similar ends, or sum by in conjunction with case statement.

Joel Acha · Answer

You'll need to create level-based measures.

Robert Angel · Answer

Hi,

just duplicate your measure and drag it to the Year level of your time hierarchy.

This will make that version of your measure invariant for anything lower than year in your reports.

You can then use this as the basis of your YAGO measure.