How to Calculate Average Daily Revenue Based on User Date Filters in SAP Analytics Cloud?

[kastolia]

I want to create a story in SAP Analytics Cloud that displays the average daily revenue based on calendar days and the date filter that the user applies.

Specifically, in the model, I have records at the monthly level and amounts. Depending on the filter the user sets in the date field, I want to calculate the average revenue based on the number of days in the period specified by the user in the filter. How could this be done in SAC?

[N1kh1l · Accepted solution]

@kastolia 

You can try something like this. Since you have data at monthly levels only, the key is no of days in each month. Now there is no readymade option in SAC to provide you this but you can try getting this no of days in 2 steps. First create a calculated measure/account like below.

Month Days

IF([d/"BestRunJuice_SampleModel":Date_703i1904sd].[p/MONTHDESC]=('Jan', 'Mar', 'May', 'Jul', 'Aug', 'Oct', 'Dec'), 31, IF([d/"BestRunJuice_SampleModel":Date_703i1904sd].[p/MONTHDESC]=('Apr', 'Jun', 'Sep', 'Nov'), 30, 28))

 I am not checking for leap year but you could do that with an extra IF on year first.

Create another calculated account/measure of type aggregation using this previous calculation

Finally you can create your required calculated by dividing the Revenue by this previous aggregated account/measure.

Final result

Hope this helps !!

Nikhil

[kastolia · Accepted solution]

It works! Many thanks for your help!