How to create based on the Quarter last month sales in Chart?

[chandrasekhar6]

Hi Team,

Our Storyboard contains the following fields,

Reporting Month, Sales Actual, Sales Budget

How to create the below scenario in the chart,

Our scenario: If I select Storyboard Filter is Reporting Month as April 2022 then in the chart to show the

1^st bar is Last Year's of December (Dec 2021) Sales Actual,

2^nd bar is Previous of Quarter Last Month (Mar 2022) Sales Actual,

3^rd bar is Selection Month is (Apr 2022) Sales Actual,

4^th bar is the Current Quarter of Last Month (Jun 2022) Sales Budget,

5^th Bar is Next Quarter of Last Month (Sep 2022) Sales Budget,

6^th Bar (last) is the Last Quarter of the Last Month (Dec 2022) Sales Budget.

Thanks,

Chandrasekhar

[roy_jin]

Hi Chandra,

My answer could be a little tricky, but here's my thought.

You requirement is to compare versions (Actual vs Budget), but each version will show different periods. This is a table layout we called it 'asymmetric report' which is not supported in SAC yet. So to achieve this, you may have to use calculated measure to define the value for each column. But the time navigation in restricted measure is not flexible enough to achieve you requirement.

So I will use the calculated measure in an import model with new model type.

I first create a set of calculated measure with different time offsets, for example:

Offset_P4 = RESTRICT([SignedData] ,[d/Date]=PREVIOUS("MONTH",4 ))

Offset_N1 = RESTRICT([SignedData] ,[d/Date]=NEXT("MONTH",1 ))

The offset should cover from P12~N11.

Then I will use the IF statement to decide which offset period is required. For example for your first column, it's the last period of previous year, then the formula will look like this:

Measure_1 = IF([d/Date].[p/MONTHDESC]="Jan" ,[Offset_P1] , IF([d/Date].[p/MONTHDESC]="Feb" ,[Offset_P2] , [embedded IF statements] ........))

You just need to list all possible cases based on the MONTHDESC from Jan to Dec.

Similarly, for the second column, you can define another calculated measure with similar logic.

Then in the story, you can create story calculation using restrict measure based on these calculated measures with additional restrictions with version.

I created a simple example to verify this idea. You may check the below screenshot. In this example, I'm using P4 for Actual and P1 for Plan.

Please check it.

best regards, Roy

[chandrasekhar6]

Thanks, Joy for sharing the above process.

If I select Apr, then I need to show both Apr Singed data also( here I need to show 1st Quarter last month Mar, 2nd Quarter last month Jun, 3rd Quarter last month Sep and 4th Quarter last month Dec)

[roy_jin]

Hi Chandra,

What I shown is just an example to verify my idea. To achieve your requirement, you will need to create 6 different calculated measure, and for each one, just use the similar logic. In these calculated measure, the current period without offset is just one of them to show Apr data in your example.

best regards, Roy