Problem with comparing actual vs budget in bar graph

[#1]

Hi there,

II may have missed it but I couldn’t find a solution for my need to have a bar graph that will compare actuals sales to budgeted sales.

Here is the situation:

I have a table with invoice information (invoice date, amount…). In the load process I have created Year, Month, YearMonth fields with no problem.

I have a table with budget information. I only have Year, Month and amount as fields. I created a BudgetDate with MakeDate(YearBudget,MonthBudget,).

I need to create a bar graph that will present invoice sum as well as budgeted sales f by YearMonth. Sounds easy…

I have create the bar graph with sum of invoice by YearMonth with no problem. I use the dimension YearMonthInvoice and I have a measure Sum of sales which is Sum({$<[YearInvoiceDate]= >}[Job Contract Amount]) in order to present full data even if a year selection is done.

I also have a measure Budgeted Sales which is causing problem.

If I do sum({<YearBudget={'$(=max(YearInvoiceDate))'}>}MontantBudget), I get the same amount of budget for each month.

If I do sum({<MonthYearBudget={'$(=max(MonthYearInvoice))'}>}MontantBudget), I don’t get any data for budget.

I noticed that by changing my dimension from YearMonthInvoice to YearMonthBudget, the budget part is working fine but the actual sales goes wrong.

I suspect I might have to create a relationship between Budget and Sales dates… or use a master calendar. But I also have many other dates (booking, delivery, closure…) which I may need to use to do comparison and I need to understand how to manipulate dates better.

You help would be greatly appreciated. To help understanding the situation, I have attached the apps. Problem is in the first sheet named Global.

  Thanks in advance !

[robert99]

You need a common YearMonth

The way I do it is

Concatenate Actual and budget in one table. (with a new say Type field as Budget and Actual)

And then use the date field in the concatenated table to create and YearMth field.

With Type = {Budget} in one expression and

Type ={Actual} in another one

[sunny_talwar]

What you need is a Link Table which would combine the two dates to a common calendar. Try something like this:

Actual:

LOAD RowNo() as ActualKey,

          yourFields,

          InvoiceDate

FROM ....

Budget:

LOAD RowNo() as BudgetKey,

          yourFields,

          BudgetDate

FROM ...

LinkTable:

LOAD ActualKey,

          InvoiceDate as Date,

          'Actual Date' as DateType

Resident Actual;

Concatenate (LinkTable)

LOAD BudgetKey,

          BudgetDate as Date,

          'Budget Date' as DateType

Resident Budget;

Temp:

LOAD Min(Date) as minDate,

  Max(Date) as maxDate

Resident LinkTable;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

  $(vMinDate) + IterNo() - 1 as Num,

  Date($(vMinDate) + IterNo() - 1) as TempDate

AutoGenerate

  1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar:

LOAD

  TempDate as Date,

  Week(TempDate) as Week,

  Year(TempDate) as Year,

  Month(TempDate) as Month,

  Day(TempDate) as Day,

  Weekday(TempDate) as WeekDay,

  'Q' & ceil(month(TempDate) / 3) as Quarter,

  'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,

  MonthName(TempDate) as MonthYear,

  Week(TempDate)&'-'&Year(TempDate) as WeekYear

Resident TempCalendar

Order By TempDate ASC;

DROP Table TempCalendar;

LET vMinDate = Null();

LET vMaxDate = Null();

You can find more details on the idea I used above in the following blog by hic

Canonical Date

I hope this helps.

Best,

Sunny

[#4]

Hi Sunny and RJ for your prompt reaction.

I tried RJ recommendation with concatenate the budget and the invoice date in a date table. I succeeded in creating the table but the bar graph still didn't work.

So I tried implementing Sunny solution. I succeeded in concatenate the BridgeDate table and create the MasterCalendar (at least I think...).

But the bar graph is still not working... I don't have any bar showing up. The graph is just empty.

I really dont understand because my Actual Sales is using the expression adviced in the post from HIC:

Sum({<DateType = "Date facture">}[Job Contract Amount])

And Budgeted Sales is: sum({<DateType = "Date Budget">}MontantBudget)

I'm really getting crazy with this... I can't find what I do wrong. Below is the part of my load script related to this problem, and the app.

Your help would be greatly appreciated.

Best regards,

Patrick

LOAD
    BudgetKey,
    MakeDate(YearBudget,MonthBudget,) as BudgetDate,
    MontantBudget
FROM [lib://Offre Reporting/150529 AH Budget.xlsx]
(ooxml, embedded labels, table is Budget);

DateBridge:
Load
     ID_FACT,
     'Date facture' as DateType,
     InvoiceDate as BridgeDate
Resident Facturation;
 
concatenate (DateBridge)
Load
     BudgetKey,
     'Date Budget' as DateType,
     BudgetDate as BridgeDate
Resident Budget;

Temp:
LOAD Min(BridgeDate) as minDate,
  Max(BridgeDate) as maxDate
Resident DateBridge;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));
LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
  $(vMinDate) + IterNo() - 1 as Num,
  Date($(vMinDate) + IterNo() - 1) as TempDate
AutoGenerate
  1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar:
LOAD
  TempDate as Date,
  Year(TempDate) as Year,
  Month(TempDate) as Month,
  Day(TempDate) as Day,
  MonthName(TempDate) as MonthYear,
  Week(TempDate)&'-'&Year(TempDate) as WeekYear
Resident TempCalendar
Order By TempDate ASC;

DROP Table TempCalendar;

LET vMinDate = Null();
LET vMaxDate = Null();

[sunny_talwar]

Your master calendar should have the same date name as your DateBridge table. Right now they are not. They are two different names. I think once you make them the same, your chart should show up. (

MasterCalendar:
LOAD
  TempDate as BridgeDate Date, //Change Date to BridgeDate here...
  Year(TempDate) as Year,
  Month(TempDate) as Month,
  Day(TempDate) as Day,
  MonthName(TempDate) as MonthYear,
  Week(TempDate)&'-'&Year(TempDate) as WeekYear
Resident TempCalendar
Order By TempDate ASC;

DROP Table TempCalendar;

I hope this will help...

Best,

Sunny

[robert99]

Hi Patrick

If you do it my way you need to concatenate the budget as follows

I have a table with invoice information (invoice date, amount…). In the load process I have created Year, Month, YearMonth fields with no problem.

I have a table with budget information. I only have Year, Month and amount as fields.

The budget must be loaded as

'Budget' as Type,  ///when loading invoice data add  'Actual' as Type

makedate (01, Month,Year) as Invoice Date,  //??or something like this to create a date in the correct format

MonthBudget as JobContractAmount,

monthYear (makedate (01, Month,Year)) as MonthYear

then do two formula's and one dimension

Dimension = MonthYear

Formula Actual ---> Sum ({<Type = {Actual}>}JobContractAmount

Formula Budget ---> Sum ({<Type = {Budget}>}JobContractAmount

[#7]

Hi Sunny,

You were right about the fieldname error. I corrected it and now have the master calendar working fine.

But the bar graph didn't work in first instance... After many attemps, I found a way to get what I wanted to achieve. The expressions I used are:

Actual sales : Sum({$<[Year]= >}[Invoice Amount])

Budgeted sales : sum({$<[Year]= >}BudgetAmount)

With a dimension being YearMonth from the MasterCalendar.

There might be other ways to get same result, but at least it is working for me.

Thanks a lot for you precious help Sunny!

In case it would help someone else, here is the final loading script:

LOAD

    ID_MISSION,

    ID_FACT,

    TYPE_FACT,

    "Num Facture" as "Invoice Number",

    "Intitulé Facture" as "Invoice Description",

    Règlement as Paid,

    "Date Règlement" as DatePaid,

    "Date facture" as InvoiceDate,

    Year("Date facture") as YearInvoiceDate,

    Month("Date facture") as MonthInvoiceDate,

    dual(Year("Date facture") & Month("Date facture"),Year("Date facture") & num(Month("Date facture"))) as YearMonthInvoice

FROM [lib://Offre Reporting/Base AH.xlsx] ooxml, embedded labels, table is Facturation);

LOAD
    BudgetKey,
    MakeDate(YearBudget,MonthBudget,) as BudgetDate,
    MontantBudget
FROM [lib://Offre Reporting/150529 AH Budget.xlsx] (ooxml, embedded labels, table is Budget);

DateBridge:
Load
     ID_FACT,
     'Date facture' as DateType,
     InvoiceDate as BridgeDate
Resident Facturation;
 
concatenate (DateBridge)
Load
     BudgetKey,
     'Date Budget' as DateType,
     BudgetDate as BridgeDate
Resident Budget;

Temp:
LOAD Min(BridgeDate) as minDate,
  Max(BridgeDate) as maxDate
Resident DateBridge;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));
LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;

TempCalendar:
LOAD
  $(vMinDate) + IterNo() - 1 as Num,
  Date($(vMinDate) + IterNo() - 1) as TempDate
AutoGenerate
  1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

MasterCalendar:
LOAD
  TempDate as BridgeDate,
  Year(TempDate) as Year,
  Month(TempDate) as Month,
  Day(TempDate) as Day,
  dual(Year(TempDate) & Month(TempDate), Year(TempDate) & num(Month(TempDate))) as YearMonth
Resident TempCalendar
Order By TempDate ASC;

DROP Table TempCalendar;

LET vMinDate = Null();
LET vMaxDate = Null();

[sunny_talwar]

That's awesome

I am glad you were able to finally make it to work.

Best,

Sunny

[#9]

Hi RJ,

Thanks a lot for your help. Now that I spent quite some time in getting Sunny solution working, I see better how to use your solution.

I feel yours would have been simpler, only using dates coming from actuals and from budget. I will keep this in mind for future needs.

But Sunny solution has advantages to provide with a master calendar, fulfilling with dates even if not present in actuals or budget. As you can see I did carefully my readings of Canonical Date post.

Thanks again for your help.

Best

[robert99]

That's great that you have got it to work

You might like to also read this.

Link table v Concatenate for fact tables

It's personal preference but I tried both (link v concatenate) and eventually went the concatenate route unless there was a good reason not to (and there often is). In part because its easier to follow but also I tend to follow Henric's advice

So budget I always concatenate but two large fact tables with totally different fields eg Logged a service request call with a entry date  and then doing the work for the call with a visit date. I would use a link table (canonical Date) and create a master calender

Please remember to mark your question as answered. Even if you link it to your answer.