Jump to content

Aldo Report: Difference between revisions

From PBI Reporting Wiki
← Older edit
VisualWikitext
 
(5 intermediate revisions by the same user not shown)
Line 373: Line 373:
     ),
     ),
     0
     0
)</pre>||
)</pre>||'''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.
|-
|-
| TBC ||<pre>TBC</pre>|| TBC
| Booked GP (EUR) ||<pre>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
)</pre>|| '''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.
|-
|-
| TBC ||<pre>TBC</pre>|| TBC
| Booked Customers ||<pre>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
)
</pre>|| '''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) ||<pre>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
)</pre>|| '''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'''.
|}
|}

Latest revision as of 14:04, 2 December 2025

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.
Retrieved from "https://wiki-reporting.qms-portal.aero/index.php?title=Aldo_Report&oldid=458"