Jump to content

Quarterly: Difference between revisions

From PBI Reporting Wiki
← Older edit
VisualWikitext
 
(10 intermediate revisions by the same user not shown)
Line 132: Line 132:
! style="width: 20%;" | Reference  !! style="width: 40%;" | Calculation  !! style="width: 40%;" | Description
! style="width: 20%;" | Reference  !! style="width: 40%;" | Calculation  !! style="width: 40%;" | Description
|-
|-
| Total Quotes ||<pre>Total Quotes =  
| Total Quotes Previous Year ||<pre>Previous Year Total QuotesB =  
COALESCE(
COALESCE(
CALCULATE(
    CALCULATE(
    DISTINCTCOUNT(Quotation[id]),
        DISTINCTCOUNT(Quotation[id]),
    NOT ISBLANK(Quotation[quoted_at]),
        FILTER(
    Quotation[type]=8),0)</pre>|| Returns the number of unique quotes that were actually issued (i.e., where a quoted timestamp exists).
            Quotation,
'''Logic'''
            NOT ISBLANK(Quotation[quoted_at]) &&
 
            Quotation[type] = 8 &&
The measure counts distinct <code>Quotation[id]</code> values that satisfy both of the following conditions:
            YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1
 
        )
* '''Quoted timestamp is not blank''' (<code>quoted_at</code> is not null)
    ),
* '''Quotation type equals 8'''
0)</pre>|| 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.
 
If no records meet these criteria, the measure returns '''0'''.
|-
|-
| Total Quotes ||<pre>Total Quotes =  
| Total Booking Previous Year ||<pre>Previous Year Total BookingB =  
VAR StartPrevYear =
    DATE( YEAR(TODAY()) - 1, 1, 1 )
VAR StartCurrentYear =
    DATE( YEAR(TODAY()), 1, 1 )
RETURN
COALESCE(
COALESCE(
CALCULATE(
    CALCULATE(
    DISTINCTCOUNT(Quotation[id]),
        DISTINCTCOUNT(Quotation[id]),
    NOT ISBLANK(Quotation[quoted_at]),
        NOT ISBLANK(Quotation[booked_at]),
    Quotation[type]=8),0)</pre>|| Returns the number of unique quotes that were actually issued (i.e., where a quoted timestamp exists).
        Quotation[deleted_at] = BLANK(),
'''Logic'''
        Quotation[lost_at] = BLANK(),
 
        Quotation[type] = 8,
The measure counts distinct <code>Quotation[id]</code> values that satisfy both of the following conditions:
        Quotation[created_at] >= StartPrevYear,
 
        Quotation[created_at] <  StartCurrentYear
* '''Quoted timestamp is not blank''' (<code>quoted_at</code> is not null)
    ),
* '''Quotation type equals 8'''
0)</pre>|| 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.
 
If no records meet these criteria, the measure returns '''0'''.
|-
|-
| Converted Bookings ||<pre>Converted BookingB =  
| Previous Year Chargeable Weight Quoted kg ||<pre>Previous Year Chargeable Weight Quoted (kg) =  
COALESCE(
COALESCE(
CALCULATE(
    CALCULATE(
    DISTINCTCOUNT(Quotation[id]),
        SUM(Quotation[reporting_db quote_revenue.chargeable_weight]),
    ISBLANK(Quotation[quoted_at]),
        FILTER(
    NOT ISBLANK(Quotation[booked_at]),
            Quotation,
    Quotation[type] = 8
            NOT ISBLANK(Quotation[reporting_db quote_revenue.quoted_at]) &&
),0)</pre>|| The measure counts distinct <code>Quotation[id]</code> values that meet all of the following conditions:
            YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1
 
        )
* '''Quoted timestamp is blank''' (<code>quoted_at</code> is null)
    ),
* '''Booked timestamp is not blank''' (<code>booked_at</code> is not null)
0
* '''Quotation type equals 8'''
)</pre>|| 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.
 
If no records meet these criteria, the measure returns '''0'''.
|-
|-
| Quoted Customers ||<pre>Quoted Customers =  
| Previous Year Quoted Customers ||<pre>Previous Year Quoted CustomersB =  
COALESCE(
COALESCE(
CALCULATE(
    CALCULATE(
    DISTINCTCOUNT(Quotation[customer_id]),
        DISTINCTCOUNT(Quotation[customer_id]),
    NOT ISBLANK(Quotation[quoted_at])&&  
        FILTER(
    NOT ISBLANK(Quotation[id]),
            Quotation,
    Quotation[type]=8),0)</pre>|| Calculates the number of unique customers who received at least one valid quote
            NOT ISBLANK(Quotation[quoted_at]) &&
The measure counts distinct <code>Quotation[customer_id]</code> values where all of the following conditions are met:
            NOT ISBLANK(Quotation[id]) &&
 
            Quotation[type] = 8 &&
* '''Quoted timestamp is not blank''' (<code>quoted_at</code> is not null)
            YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1
* '''Quotation ID is not blank''' (<code>id</code> is not null)
        )
* '''Quotation type equals 8'''
    ),
 
0)</pre>|| 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.
If no records match these conditions, the measure returns '''0'''.
|-
|-
| Won Quote % ||<pre>Won Quote24 % =  
| Previous year Revenue quoted euro ||<pre>Previous Year Revenue Quoted EUR =  
COALESCE(
COALESCE(
     VAR TotalQuotes =
     CALCULATE(
        CALCULATE(
        SUM(Quotation[reporting_db quote_revenue.Quoted_customer_invoice_currency_sell_total_Euro]),
            DISTINCTCOUNT(Quotation[id]),
        FILTER(
            NOT ISBLANK(Quotation[quoted_at]),
             Quotation,
             Quotation[type] = 8,
             NOT ISBLANK(Quotation[quoted_at]) &&
             ISBLANK(Quotation[deleted_at]),
             YEAR(Quotation[created_at]) = YEAR(TODAY()) - 1
             ISBLANK(Quotation[lost_at]),
            Quotation[created_at] >= DATE(2024, 1, 1)
         )
         )
 
    ),
     VAR ConvertedBookings =
0)
         CALCULATE(
</pre>|| 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.
             DISTINCTCOUNT(Quotation[id]),
|-
            NOT ISBLANK(Quotation[quoted_at]),
| Previous year Gross profit euro quoted ||<pre>Previous Year Quoted Gross Profit (EUR) =
            NOT ISBLANK(Quotation[booked_at]),
COALESCE(
             Quotation[type] = 8,
     SUMX(
             ISBLANK(Quotation[deleted_at]),
         FILTER(
             ISBLANK(Quotation[lost_at]),
            'Dimreporting_db quotation_totals',
             Quotation[created_at] >= DATE(2024, 1, 1)
            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)
</pre>|| 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 ||<pre>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)
</pre>|| 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 ||<pre>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
)</pre>|| 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 ||<pre>Current YTD Total Quotes =
COALESCE(
    CALCULATE(
        DISTINCTCOUNT(Quotation[id]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[quoted_at]) &&
            Quotation[type] = 8
        ),
        DATESYTD(Quotation[quoted_at])
    ),
    0
)</pre>|| 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 ||<pre>Current YTD Total Booking =
COALESCE(
    CALCULATE(
        DISTINCTCOUNT(Quotation[id]),
        FILTER(
            Quotation,
            NOT ISBLANK(Quotation[booked_at]) &&
            Quotation[type] = 8
        ),
        DATESYTD(Quotation[booked_at])
    ),
    0
)</pre>|| 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.


    RETURN DIVIDE(ConvertedBookings, TotalQuotes, 0)
,0)</pre>|| The formula calculates the conversion rates of quotes showing quotes that successfully resulted in booked or confirmed bookings.
|-
|-
| Direct  Booking ||<pre>Direct Booking =  
| Booked Customers Current year ||<pre>Current YTD Booked Customers =  
COALESCE(
COALESCE(
CALCULATE(
    CALCULATE(
    DISTINCTCOUNT(Quotation[id]),
        DISTINCTCOUNT('Quotation'[customer_id]),
    NOT ISBLANK(Quotation[quoted_at]),
        FILTER(
    NOT ISBLANK(Quotation[booked_at]),
            'Quotation',
    Quotation[type] = 8
            NOT ISBLANK('Quotation'[customer_id]) &&
),0)</pre>|| <code>Direct BookingB</code> measures the number of unique quotes that were directly converted into bookings. These are cases where a quote was issued and subsequently booked without being lost or deleted.  
            NOT ISBLANK('Quotation'[booked_at]) &&
            ISBLANK('Quotation'[lost_at]) &&
            ISBLANK('Quotation'[deleted_at])
        ),
        DATESYTD('Quotation'[booked_at])
    ),
    0
)</pre>|| 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.  
 
|-
|-
| Chargeable weight quoted in kg ||<pre>Chargeable_Weight quoted kg =  
| Routes (o +D) ||<pre>Origin_dest_Coount quoted =  
COALESCE(
COALESCE(
CALCULATE(
CALCULATE(
     SUM(Quotation[chargeable_weight_kg]),
     DISTINCTCOUNT(Quotation[Origin _ Destination]),
     NOT ISBLANK(Quotation[quoted_at]),
     NOT ISBLANK(Quotation[quoted_at]),
     Quotation[type]=8),0)</pre>|| <code>Chargeable_Weight Quoted (kg)</code> calculates the total chargeable weight, in kilograms, associated with all valid quoted shipments.  
     Quotation[type] =8),0)</pre>|| This measure calculates the number of unique <code>Origin _ Destination</code> entries from the <code>Quotation</code> table where a quotation has been provided (<code>quoted_at</code> is not blank) and the quotation <code>type</code> equals 8. If no such entries exist, the measure returns 0.  
 
|-
|-
| Total Revenue quoted in euro ||<pre>Revenue Quoted EUR =  
| Routes (o +D) ||<pre>Origin_dest_Coount quoted =  
COALESCE(
COALESCE(
  CALCULATE(
  CALCULATE(
     SUM('Quotation'[reporting_db quote_revenue.Quoted_customer_invoice_currency_sell_total_Euro]),
     DISTINCTCOUNT(Quotation[Origin _ Destination]),
     NOT ISBLANK(Quotation[quoted_at])),0)</pre>|| <code>Revenue Quoted (EUR)</code> calculates the total quoted revenue in euros for all valid quotes.
     NOT ISBLANK(Quotation[quoted_at]),
|-
    Quotation[type] =8),0)</pre>|| This measure calculates the number of unique <code>Origin _ Destination</code> entries from the <code>Quotation</code> table where a quotation has been provided (<code>quoted_at</code> is not blank) and the quotation <code>type</code> equals 8. If no such entries exist, the measure returns 0.  
| Total Margin% ||<pre>Margin (%) =
DIVIDE(
    [Total Revenue (EUR Booked or Confirmed)] - [Total Cost (EUR booked or confirmed)],
    [Total Revenue (EUR Booked or Confirmed)],
    0
) </pre>|| <code>Margin (%)</code> calculates the profitability percentage of booked or confirmed jobs by comparing revenue against cost.  
|-
|-
| Routes (o +D) ||<pre>Origin_dest_Coount quoted =  
| Routes (o +D) ||<pre>Origin_dest_Coount quoted =  
Line 252: Line 310:
     DISTINCTCOUNT(Quotation[Origin _ Destination]),
     DISTINCTCOUNT(Quotation[Origin _ Destination]),
     NOT ISBLANK(Quotation[quoted_at]),
     NOT ISBLANK(Quotation[quoted_at]),
     Quotation[type] =8),0)</pre>|| This measure calculates the number of unique <code>Origin _ Destination</code> entries from the <code>Quotation</code> table where a quotation has been provided (<code>quoted_at</code> is not blank) and the quotation <code>type</code> equals 8. If no such entries exist, the measure returns 0.  
     Quotation[type] =8),0)</pre>|| This measure calculates the number of unique <code>Origin _ Destination</code> entries from the <code>Quotation</code> table where a quotation has been provided (<code>quoted_at</code> is not blank) and the quotation <code>type</code> equals 8. If no such entries exist, the measure returns 0.


|}
|}

Latest revision as of 09:51, 28 November 2025

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"