Jump to content

Quarterly

From PBI Reporting Wiki

Summary

This page provides a comprehensive historical overview of quotes (type = 8), excluding lost and deleted jobs, on quarterly basis both current and previous year. It highlights key business KPIs, including total quotes, converted bookings, quoted customers, won quotes percentage, direct bookings, chargeable weight, revenue, gross profit, routes, site and margin

Filters

Filter Description
Sites Filter quotation values by site name

Data Transformation Applied

[To be added]

Query Calculations Description
Quote revenue from reporting database SELECT * FROM reporting_db_quote_revenue The query retrieves data from the quote revenue table in the reporting database.
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.
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)
 This measure returns the number of distinct quotations created and quoted in the previous calendar year for quotation type 8. It focuses strictly on quotations that have a valid quotation date, ensuring only legitimate issued quotations are counted.
Total Booking Previous Year 
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)
 This measure returns the number of distinct quotations booked in the previous calendar year. It filters out deleted or lost quotations and restricts the count to a specific quotation type. The calculation ensures year-over-year reporting aligns with the exact boundaries of the previous and current years.
Previous Year Chargeable Weight Quoted kg 
Previous Year Chargeable Weight Quoted (kg) = 
COALESCE(
    CALCULATE(
        SUM(Quotation[reporting_db quote_revenue.chargeable_weight]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[reporting_db quote_revenue.quoted_at]) &&
            YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1
        )
    ),
0
)
 This measure calculates the total quoted chargeable weight (in kilograms) for all quotations created in the previous calendar year. Only quotations with a valid quotation date are included, ensuring the metric reflects confirmed, issued quotations for the period.
Previous Year Quoted Customers 
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)
 This measure calculates the number of distinct customers who received quotations in the previous calendar year. It considers only valid, issued quotations of type 8, ensuring an accurate count of actively quoted customers.
Previous year Revenue quoted euro 
Previous Year Revenue Quoted EUR = 
COALESCE(
    CALCULATE(
        SUM(Quotation[reporting_db quote_revenue.Quoted_customer_invoice_currency_sell_total_Euro]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[quoted_at]) &&
            YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1
        )
    ),
0)
This measure calculates the total quoted revenue in euros for all quotations created in the previous calendar year. Only quotations with a valid quotation date are included, ensuring the metric reflects actual issued quotations.
Previous year Gross profit euro quoted 
Previous Year Quoted Gross Profit (EUR) = 
COALESCE(
    SUMX(
        FILTER(
            'Dimreporting_db quotation_totals',
            NOT ISBLANK('Dimreporting_db quotation_totals'[quoted_at]) &&
            YEAR('Dimreporting_db quotation_totals'[created_at]) = YEAR(TODAY()) - 1
        ),
        ('Dimreporting_db quotation_totals'[customer_invoice_currency_sell_total]
         - 'Dimreporting_db quotation_totals'[customer_invoice_currency_cost_total])
        / 'Dimreporting_db quotation_totals'[quoted_customer_invoice_currency_to_euro_exchange_rate]
    ),
0)
This measure calculates the total quoted gross profit in euros for all quotations created in the previous calendar year. Only quotations with a valid quotation date are included, and each quotation’s gross profit is converted to EUR using its specific quoted exchange rate.
Previous Year Booked Customers 
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)
This measure calculates the number of distinct customers who received quotations in the previous calendar year for quotation type 8. Only valid, issued quotations are counted, providing a clear view of customer engagement in the prior year.
Previous Year Gross Profit euro 
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
)
 This measure calculates the total gross profit in euros for quotations that were booked or confirmed in the previous year. It includes only quotations with a valid quotation date and applies a year-over-year time filter using the calendar table.
Total Quotes Current Year 
Current YTD Total Quotes = 
COALESCE(
    CALCULATE(
        DISTINCTCOUNT(Quotation[id]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[quoted_at]) &&
            Quotation[type] = 8
        ),
        DATESYTD(Quotation[quoted_at])
    ),
    0
)
 This measure calculates the year-to-date (YTD) total number of distinct quotations for the current year, considering only valid quotations of type 8. It provides a running count of quotations issued from the start of the current year up to today.
Total booking Current Year 
Current YTD Total Booking = 
COALESCE(
    CALCULATE(
        DISTINCTCOUNT(Quotation[id]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[booked_at]) &&
            Quotation[type] = 8
        ),
        DATESYTD(Quotation[booked_at])
    ),
    0
)
 This measure calculates the year-to-date (YTD) total number of bookings for the current year, considering only valid quotations of type 8 that have been booked. It provides a running count of confirmed bookings from the start of the year up to the current date.
Booked Customers Current year 
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
)
 This measure calculates the year-to-date (YTD) total number of distinct customers with booked quotations for the current year. It excludes lost or deleted quotations to ensure the metric reflects active, confirmed bookings.
Routes (o +D) 
Origin_dest_Coount quoted = 
COALESCE(
 CALCULATE(
    DISTINCTCOUNT(Quotation[Origin _ Destination]),
    NOT ISBLANK(Quotation[quoted_at]),
    Quotation[type] =8),0)
 This measure calculates the number of unique Origin _ Destination entries from the Quotation table where a quotation has been provided (quoted_at is not blank) and the quotation type equals 8. If no such entries exist, the measure returns 0.
Routes (o +D) 
Origin_dest_Coount quoted = 
COALESCE(
 CALCULATE(
    DISTINCTCOUNT(Quotation[Origin _ Destination]),
    NOT ISBLANK(Quotation[quoted_at]),
    Quotation[type] =8),0)
 This measure calculates the number of unique Origin _ Destination entries from the Quotation table where a quotation has been provided (quoted_at is not blank) and the quotation type equals 8. If no such entries exist, the measure returns 0.
Routes (o +D) 
Origin_dest_Coount quoted = 
COALESCE(
 CALCULATE(
    DISTINCTCOUNT(Quotation[Origin _ Destination]),
    NOT ISBLANK(Quotation[quoted_at]),
    Quotation[type] =8),0)
 This measure calculates the number of unique Origin _ Destination entries from the Quotation table where a quotation has been provided (quoted_at is not blank) and the quotation type equals 8. If no such entries exist, the measure returns 0.
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Quarterly&oldid=420"