Actual vs Budget Scenario

[ritvik4BI]

Hi everyone!

So here is a scenario where i am stuck. I will try to be as descriptive as possible.

I have two sheets. Actual and Budget. Each sheet has 5 columns: SDU, ProductName, CalenderDate, DistributionChannel and Value. Now the catch is that the Actual table gets uploaded on a daily basis whereas the Budget gets uploaded once every month. 

I have attached the sample excel workbook which has two sheets that I am using for this application.

MY problem: When in the front end i try to filter by product and date lets say the budget comes on the 5th Jan every month for all products. But when I am selecting any product and date say 7th Jan. It is showing Budget as zero. Ideally it should  give my the Budget value for that particular product in that  particular month irrespective of my selection.

Here is my code:

Actual:
LOAD
SDU,
ProductName,
CalenderDate as Date,
DistributionChannel,
"Actual Value" as Value,
'Actual' as vflag
FROM [lib://AttachedFiles/Actual vs Budget Scenario.xlsx]
(ooxml, embedded labels, table is Actual);

Concatenate
LOAD
SDU,
ProductName,
CalenderDate as Date,
DistributionChannel,
"Budget Value" as Value,
'Budget' as vflag

FROM [lib://AttachedFiles/Actual vs Budget Scenario.xlsx]
(ooxml, embedded labels, table is Budget);

MinMax:
LOAD
Date(min(Date),'DD/MM/YY') as MinDate,
Date(Max(Date),'DD/MM/YY') as MaxDate
Resident Actual;

Let varMinDate = Num(Peek('MinDate',-1,'MinMax'));
Let varMaxDate = Num(Peek('MaxDate',-1,'MinMax'));

Drop Table MinMax;
//*********Creating table Master_Calender****************
Master_Calendar:
Load
*,
TempDate as Date,
Month(TempDate) as Month,
Year(TempDate) as Year;
//QuarterName(TempDate) as Quarter,
//'Quarter - ' & Ceil(Month(TempDate)/3) AS Quarter_No;

Load

Date($(varMinDate) + (IterNo()-1),'DD/MM/YYYY') as TempDate

AutoGenerate 1

While Date($(varMinDate) + (IterNo()-1)) <= Date($(varMaxDate));

Drop Field TempDate
From Master_Calendar;

End Script;

In the front end expression that i used to get Budget and Actual value is: Sum({<vflag={'Actual'}>}Value

and Sum({<vflag={'Budget'}>}Value

Here is a snap of my front end:

Like here i am selecting Product as Notebook and date as 6th jan. The Actual Value is correct. Budget value it should show me 3000 but is showing. Refer to excel attached.

Your feedback is highly appreciated.

Thanks and Regards.

[Vegar · Accepted solution]

You could achieve this with this expression:

Sum(total <DistributionChannel, ProductName> {<Date, Month={'$(=month(Date))'},Year={$(=year(Date))}, vflag={'Budget'}>}Value)

Have you considered remodelling your data model? You could make these calculations easier linked your transaction to a calendar using intervalmatch().

Actual:
LOAD
SDU,
ProductName,
CalenderDate as Date_from,
CalenderDate as Date_to,
monthname(CalenderDate) as %period,
DistributionChannel,
"Actual Value" as Value,
'Actual' as vflag
FROM [lib://Downloads/Actual vs Budget Scenario.xlsx]
(ooxml, embedded labels, table is Actual);

 

Concatenate
LOAD
SDU,
ProductName,
MonthStart(CalenderDate) as Date_from,
MonthEnd(CalenderDate) as Date_to,
DistributionChannel,
"Budget Value" as Value,
'Budget' as vflag

FROM [lib://Downloads/Actual vs Budget Scenario.xlsx]
(ooxml, embedded labels, table is Budget);

 

MinMax:
LOAD
Date(min(Date_from),'DD/MM/YY') as MinDate,
Date(Max(Date_to),'DD/MM/YY') as MaxDate
Resident Actual;

Let varMinDate = Num(Peek('MinDate',-1,'MinMax'));
Let varMaxDate = Num(Peek('MaxDate',-1,'MinMax'));


Drop Table MinMax;
//*********Creating table Master_Calender****************
Master_Calendar:
Load
TempDate as Date,
Month(TempDate) as Month,
Year(TempDate) as Year;

Load
Date($(varMinDate) + (IterNo()-1),'DD/MM/YYYY') as TempDate

AutoGenerate 1

While Date($(varMinDate) + (IterNo()-1)) <= Date($(varMaxDate));

IntervalMatch:
IntervalMatch([Date]) LOAD Date_from, Date_to Resident Actual; 
exit Script;

.  This sample will give you an syntetic key, it will make no harm to your application but you can take means to remove it if you don't want it.