Jump to content

Overall YTD

From PBI Reporting Wiki

Summary

  Purpose

This page provides a comprehensive Year-To-Date (YTD) and historical analysis of Booked and confirmed bookings (type = 8), excluding lost and deleted jobs. It focuses on key business KPIs such as jobs, customers, chargeable weight, revenue, gross profit, and margin

Filters

Filter Description
Carriers Filter by carrier name based on main carrier id
Year Filter by year booked or confirmed

Data Transformation Applied

[To be added]

Query Calculations Description
Quotation table SELECT

   q.id,

   q.booked_at,

   q.confirmed_at,

   q.quoted_at,

   q.pre_quoted_at,

   q.created_at,

   q.user_id,

   q.booking_user_id,

   q.lost_reason,

   q.customer_id,

   q.customer_code,

   q.shipper_status,

   q.main_carrier_id,

   q.deleted_at,

   q.lost_at,

   q.delete_reasons,

   q.chargeable_weight,

   q.gross_weight,

   q.spotrate,

   q.type,

   q.schedule_auto_update,

   q.automation_created_count,

   q.schedule_auto_amount,

   q.status,

   q.booking_sync_state,

   q.cargo_type,

   q.house_airwaybill,

   q.product,

   q.origin,

   q.destination,

   q.master_airwaybill,

   q.hazardous_goods,

   q.commodity_description,

   q.weight_unit,

   q.cargo_only,

   CASE

       WHEN q.weight_unit = 'lb' THEN ROUND(q.chargeable_weight * 0.453592, 2)

       ELSE q.charge_weight

   END AS chargeable_weight_kg

FROM quotations AS q

WHERE q.created_at >= '2024-01-01'

  AND q.type = 8

  AND q.lost_at IS NULL

  AND q.deleted_at IS NULL;


This query retrieves columns from the quotation table and converts the chargeable weight from pounds to kilograms. It filters records where the creation date is on or after 2024 and the type equals 8, excluding lost and deleted entries.
reporting db quotation totals SELECT * FROM reporting_db_quotation_totals This selects data from reporting_db_quotation_totals
Sites SELECT id, name FROM sites This selects site id and name from the site table.
Customer SELECT id, name FROM customers This query selects customer id and name from customer table
Users SELECT id, name FROM users This query selects User id and name from user table

Calculations

Reference Calculation Description
Total Jobs 
Total Jobs= 
CALCULATE(
    DISTINCTCOUNT('Quotation'[id]),
    FILTER(
        'Quotation',
        NOT ISBLANK('Quotation'[id])
    )
)
 The formula calculates the total number of unique jobs by quotation ID
Total Booked Customers 
Booked Customers = 
COALESCE(
CALCULATE(
    DISTINCTCOUNT('Quotation'[customer_id]),
    FILTER(
        'Quotation',
        NOT ISBLANK('Quotation'[customer_id]) &&
        (
            NOT ISBLANK('Quotation'[booked_at]) ||
            NOT ISBLANK('Quotation'[confirmed_at])
        ) &&
        ISBLANK('Quotation'[lost_at]) &&
        ISBLANK('Quotation'[deleted_at])
    )
),0)
 The formula calculates the total number of booked customers by customer id
Total Chargeable Weights in kg 
Chargeable_Weight quoted kg = 
COALESCE(
CALCULATE(
    SUM(Quotation[chargeable_weight_kg]),
    NOT ISBLANK(Quotation[quoted_at]),
    Quotation[type]=8),0)
 The formula calculates the total chargeable weight in kilograms
Total Revenue in euros booked or confirmed 
Total Revenue (EUR Booked or Confirmed) = 
COALESCE(
SUMX(
    FILTER(
        'Dimreporting_db quotation_totals',
        'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
    ),
    SWITCH(
        TRUE(),
        NOT ISBLANK('Dimreporting_db quotation_totals'[booked_at]),
            'Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total] 
            / 'Dimreporting_db quotation_totals'[booked_customer_invoice_currency_to_euro_exchange_rate],
        ISBLANK('Dimreporting_db quotation_totals'[booked_at]) 
            && NOT ISBLANK('Dimreporting_db quotation_totals'[confirmed_at]),
            'Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total] 
            / 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate],
        0
    )
),
0)
 The formula calculates the total revenue in euros where either the booked timestamp or the confirmed timestamp is not null.
Gross Profit in euros booked or confirmed 
Gross Profit (EUR booked or Confirmed)2 = 
COALESCE(
    SUMX(
        FILTER(
            'Dimreporting_db quotation_totals',
            'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
        ),
        SWITCH(
            TRUE(),
            NOT ISBLANK('Dimreporting_db quotation_totals'[booked_at]),
                ('Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total] 
                 - 'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total]) 
                / 'Dimreporting_db quotation_totals'[booked_customer_invoice_currency_to_euro_exchange_rate],
            ISBLANK('Dimreporting_db quotation_totals'[booked_at]) 
                && NOT ISBLANK('Dimreporting_db quotation_totals'[confirmed_at]),
                ('Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total] 
                 - 'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total]) 
                / 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate],
            0
        )
    ),
0)
 The formula calculates the gross profit in euros where either the booked timestamp or the confirmed timestamp is not null.
Total Cost in euros 
Total Cost (EUR booked or confirmed) = 
SUMX(
    FILTER(
        'Dimreporting_db quotation_totals',
        'Dimreporting_db quotation_totals'[created_at] >= DATE(2024, 1, 1)
    ),
    SWITCH(
        TRUE(),
        NOT ISBLANK('Dimreporting_db quotation_totals'[booked_at]),
            'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total] 
            / 'Dimreporting_db quotation_totals'[booked_customer_invoice_currency_to_euro_exchange_rate],
        ISBLANK('Dimreporting_db quotation_totals'[booked_at]) 
            && NOT ISBLANK('Dimreporting_db quotation_totals'[confirmed_at]),
            'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total] 
            / 'Dimreporting_db quotation_totals'[confirmed_customer_invoice_currency_to_euro_exchange_rate],
        0
    )
)
 The formula calculates the total cost in euros where either the booked timestamp or the confirmed timestamp is not null.
Total Margin in euros % 
Margin (%) = 
DIVIDE(
    [Total Revenue (EUR Booked or Confirmed)] - [Total Cost (EUR booked or confirmed)],
    [Total Revenue (EUR Booked or Confirmed)],
    0
)
The formula calculates the total margin in euros where either the booked timestamp or the confirmed timestamp is not null.
Origin destination

pair 

 each [origin] & " - " & [destination]
 The formula concatenate origin column and destination column
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Overall_YTD&oldid=346"