Aldo Report

Summary

Historical View of Booked and Confirmed Jobs (Type = 8)

This page provides a historical overview of booked and confirmed jobs (type = 8), excluding lost and deleted jobs, for both the current and previous year.

Filters

Filter	Description
Carriers	Filters by carrier name

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 Quotes previous Year	Previous Year Total QuotesB = COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[id]), FILTER( Quotation, NOT ISBLANK(Quotation[quoted_at]) && Quotation[type] = 8 && YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1 ) ), 0)	Previous Year Total Quotes B measures the total number of distinct quotes (type = 8) issued during the previous calendar year, excluding any records without a valid quotation date.
Quoted Customer previous year	Previous Year Quoted CustomersB = COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[customer_id]), FILTER( Quotation, NOT ISBLANK(Quotation[quoted_at]) && NOT ISBLANK(Quotation[id]) && Quotation[type] = 8 && YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1 ) ), 0)	Previous Year Quoted Customers B counts the number of unique customers who received a quote (type = 8) during the previous calendar year, excluding any records with missing quotation dates.
Previous Year Total Booking	Previous Year Total BookingB = VAR StartPrevYear = DATE( YEAR(TODAY()) - 1, 1, 1 ) VAR StartCurrentYear = DATE( YEAR(TODAY()), 1, 1 ) RETURN COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[id]), NOT ISBLANK(Quotation[booked_at]), Quotation[deleted_at] = BLANK(), Quotation[lost_at] = BLANK(), Quotation[type] = 8, Quotation[created_at] >= StartPrevYear, Quotation[created_at] < StartCurrentYear ), 0)	Previous Year Total Booking B measures the number of distinct bookings (type = 8) created during the previous calendar year, excluding any bookings that were deleted or marked as lost.
Previous Year Chargeable Weight booked kg	PreviousYear_Booked_ChargeableWeight_kg = COALESCE( CALCULATE( SUM(Quotation[reporting_db quote_revenue.chargeable_weight]), FILTER( Quotation, NOT ISBLANK(Quotation[booked_at]) && Quotation[type] = 8 && YEAR(Quotation[booked_at]) = YEAR(TODAY()) - 1 ) ), 0 )	Previous Year Chargeable Weight Booked (kg) represents the total chargeable weight from all quotations issued in the previous calendar year, based on the value recorded in reporting_db quote_revenue.chargeable_weight.
Booked Customers previous year	Previous Year Booked CustomersB = VAR StartPrevYear = DATE(YEAR(TODAY()) - 1, 1, 1) VAR StartCurrentYear = DATE(YEAR(TODAY()), 1, 1) RETURN COALESCE( CALCULATE( DISTINCTCOUNT('Quotation'[customer_id]), 'Quotation'[type] = 8, NOT ISBLANK('Quotation'[customer_id]), NOT ISBLANK('Quotation'[booked_at]) || NOT ISBLANK('Quotation'[confirmed_at]), ISBLANK('Quotation'[lost_at]), ISBLANK('Quotation'[deleted_at]), 'Quotation'[created_at] >= StartPrevYear, 'Quotation'[created_at] < StartCurrentYear ), 0)	Previous Year Booked Customers B reports the number of unique customers who had a booked or confirmed job (type = 8) during the previous calendar year, excluding any jobs marked as lost or deleted.
Booked Routes (O + D)	PreviousYear_Origin_dest_Count_Booked = COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[Origin _ Destination]), FILTER( Quotation, NOT ISBLANK(Quotation[booked_at]) && Quotation[type] = 8 && YEAR(Quotation[booked_at]) = YEAR(TODAY()) - 1 ) ), 0)	Previous Year Origin–Destination Count (Booked) measures the number of unique origin–destination pairs associated with bookings made during the previous calendar year for jobs of type 8. Calculation Logic: Included Records: booked_at is not blank (the job was booked). type = 8 (valid job type). The booking date (booked_at) falls within the previous year (YEAR(TODAY()) - 1). Aggregation: DISTINCTCOUNT(Quotation[Origin _ Destination]) counts each unique route pair once. Null Handling: COALESCE returns 0 when no qualifying bookings are found.
Booked GP (EuR) previous year	Previous Year Gross Profit (EUR booked or Confirmed) = COALESCE( CALCULATE( SUM('Quotation'[reporting_db quote_revenue.Quoted_customer_invoice_currency_sell_total_Euro]) - SUM('Quotation'[reporting_db quote_revenue.Quoted_customer_invoice_currency_cost_total_Euro]), NOT ISBLANK('Quotation'[reporting_db quote_revenue.quoted_at]), SAMEPERIODLASTYEAR('DimDate (2)'[Date]) ), 0 )	Previous Year Gross Profit (EUR — Booked or Confirmed) calculates the total gross profit generated from quotes that were booked or confirmed during the previous calendar year, expressed in Euro (EUR). Calculation Logic: Gross Profit Formula: The measure computes gross profit as: Total Sell Amount (EUR) − Total Cost Amount (EUR) using the fields: Quoted_customer_invoice_currency_sell_total_Euro Quoted_customer_invoice_currency_cost_total_Euro Included Records: quoted_at is not blank (the quote was issued). The date context is shifted to the previous year using SAMEPERIODLASTYEAR('DimDate (2)'[Date]). Aggregation: All valid sell and cost values within the shifted date period are summed. Null Handling: COALESCE ensures the measure returns 0 if no records qualify.
Booked Revenue (EUR) previous year	Previous Year Total Revenue (EUR Booked or Confirmed)B = COALESCE( SUMX( FILTER( 'Dimreporting_db quotation_totals', YEAR('Dimreporting_db quotation_totals'[created_at]) = YEAR(TODAY()) - 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)	Previous Year Total Revenue (EUR – Booked or Confirmed) B calculates the total revenue for all jobs created in the previous calendar year, converting customer invoice amounts into euros based on the appropriate exchange rate depending on whether the job was booked or confirmed.
Current YTD Total Quotes	Current YTD Total QuotesB = VAR YTD_Dates = DATESYTD('DimDate (2)'[Date]) RETURN COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[id]), Quotation[type] = 8, NOT ISBLANK(Quotation[quoted_at]), Quotation[quoted_at] IN YTD_Dates ), 0)	Current Year-to-Date (YTD) Total Quotes B counts the total number of distinct quotes (type = 8) issued so far in the current year, ensuring only valid quotations are considered.
Current YTD Routes (O + D)	CurrentYear_OriginDest_Count_Quoted = COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[Origin _ Destination]), FILTER( Quotation, NOT ISBLANK(Quotation[quoted_at]) && -- Only quoted rows Quotation[type] = 8 && -- Only type 8 YEAR(Quotation[quoted_at]) = YEAR(TODAY()) -- Current year ) ), 0 )	Current Year Origin–Destination Count (Quoted) measures the number of unique origin–destination pairs for quotations issued during the current calendar year (type = 8).
Quoted Customers Current YTD	Current YTD Quoted Customers = COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[customer_id]), FILTER( Quotation, NOT ISBLANK(Quotation[quoted_at]) && NOT ISBLANK(Quotation[id]) && Quotation[type] = 8 ), DATESYTD(Quotation[quoted_at]) ), 0	Current Year-to-Date (YTD) Quoted Customers counts the number of unique customers who have received a quote (type = 8) so far in the current year, considering only valid quotation records.
Booking	Current YTD Total Booking = COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[id]), FILTER( Quotation, NOT ISBLANK(Quotation[booked_at]) && Quotation[type] = 8 ), DATESYTD(Quotation[booked_at]) ), 0 )	Current Year-to-Date (YTD) Total Booking measures the number of distinct bookings (type = 8) made so far in the current year, considering only confirmed bookings.
Booked Routes (O + D)	CurrentYear_OriginDest_Count_Booked = COALESCE( CALCULATE( DISTINCTCOUNT(Quotation[Origin _ Destination]), FILTER( Quotation, NOT ISBLANK(Quotation[booked_at]) && -- Only booked quotes Quotation[type] = 8 && -- Only type 8 YEAR(Quotation[booked_at]) = YEAR(TODAY()) -- Current year ) ), 0 )	Current Year Origin–Destination Count (Booked) counts the number of unique origin–destination pairs for bookings (type = 8) made during the current calendar year.
Booked Revenue (EUR)	Current YTD Total Revenue (EUR Booked or Confirmed) = VAR RevenueTable = ADDCOLUMNS( 'Dimreporting_db quotation_totals', "RevenueEUR", 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 ) ) RETURN COALESCE( CALCULATE( SUMX(RevenueTable, [RevenueEUR]), DATESYTD('Dimreporting_db quotation_totals'[created_at]) ), 0 )	Current Year-to-Date (YTD) Total Revenue (EUR – Booked or Confirmed) calculates the total revenue from all quotations created in the current year-to-date, converting amounts to euros based on whether the job was booked or only confirmed.
Booked GP (EUR)	Current YTD Gross Profit (EUR Booked or Confirmed) = VAR GrossProfitTable = ADDCOLUMNS( 'Dimreporting_db quotation_totals', "GrossProfitEUR", 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 ) ) RETURN COALESCE( CALCULATE( SUMX(GrossProfitTable, [GrossProfitEUR]), DATESYTD('Dimreporting_db quotation_totals'[created_at]) ), 0 )	Current Year-to-Date (YTD) Gross Profit (EUR – Booked or Confirmed) calculates the total gross profit in euros from all quotations created in the current year-to-date, based on the difference between customer invoice sell total and cost, adjusted for whether the job was booked or only confirmed.
Booked Customers	Current YTD Booked Customers = COALESCE( CALCULATE( DISTINCTCOUNT('Quotation'[customer_id]), FILTER( 'Quotation', NOT ISBLANK('Quotation'[customer_id]) && NOT ISBLANK('Quotation'[booked_at]) && ISBLANK('Quotation'[lost_at]) && ISBLANK('Quotation'[deleted_at]) ), DATESYTD('Quotation'[booked_at]) ), 0 )	Current Year-to-Date (YTD) Booked Customers counts the number of unique customers who have a booked job (type = 8) so far in the current year, excluding any bookings that were lost or deleted.
Booked CH Weight (kg)	CurrentYTD_ChargeableWeight_Booked_kg = COALESCE( CALCULATE( SUM(Quotation[chargeable_weight_kg]), FILTER( Quotation, NOT ISBLANK(Quotation[booked_at]) && -- Only booked quotes Quotation[type] = 8 ), DATESYTD(Quotation[booked_at]) ), 0 )	Current Year-to-Date (YTD) Chargeable Weight Booked (kg) measures the total chargeable weight in kilograms from all bookings (type = 8) made so far in the current year.