Dataverse: Query data using Fiscal Period and Year

Do you need to query data with the filter based on the Month/Year based on the specified date in your records? Today, we will learn how to query it and hopefully, you can implement it in your future project!

For example, we have several records of Sales Order with the below records:

Sales Order records

In the above screenshot, you need to see the Request Delivery Date which will be used for filtering purposes.

Then, here is the below code that I used to demo the query:

{
    Console.WriteLine("In Fiscal Year 2022");
    var query = new QueryExpression("salesorder")
    {
        ColumnSet = new ColumnSet("totalamount", "requestdeliveryby", "name")
    };
    query.Criteria.AddCondition("requestdeliveryby", ConditionOperator.InFiscalYear, "2022");

    var entities = service.RetrieveMultiple(query).Entities.ToArray();
    PrintEntities(entities);
}

{
    Console.WriteLine("InOrAfterFiscalPeriodAndYear and InOrBeforeFiscalPeriodAndYear 2023");
    var query = new QueryExpression("salesorder")
    {
        ColumnSet = new ColumnSet("totalamount", "requestdeliveryby", "name")
    };
    query.Criteria.AddCondition("requestdeliveryby", ConditionOperator.InOrAfterFiscalPeriodAndYear, "01", "2023");
    query.Criteria.AddCondition("requestdeliveryby", ConditionOperator.InOrBeforeFiscalPeriodAndYear, "12", "2023");

    var entities = service.RetrieveMultiple(query).Entities.ToArray();
    PrintEntities(entities);
}

{
    // Depends on the Fiscal Settings
    Console.WriteLine("InFiscalPeriod 1");
    var query = new QueryExpression("salesorder")
    {
        ColumnSet = new ColumnSet("totalamount", "requestdeliveryby", "name")
    };
    query.Criteria.AddCondition("requestdeliveryby", ConditionOperator.InFiscalPeriod, "1");

    var entities = service.RetrieveMultiple(query).Entities.ToArray();
    PrintEntities(entities);
}

{
    Console.WriteLine("LastXFiscalYears 2");
    var query = new QueryExpression("salesorder")
    {
        ColumnSet = new ColumnSet("totalamount", "requestdeliveryby", "name")
    };
    query.Criteria.AddCondition("requestdeliveryby", ConditionOperator.LastXFiscalYears, 2);

    var entities = service.RetrieveMultiple(query).Entities.ToArray();
    PrintEntities(entities);
}

{
    // Depends on the Fiscal Settings
    Console.WriteLine("LastXFiscalPeriods 10");
    var query = new QueryExpression("salesorder")
    {
        ColumnSet = new ColumnSet("totalamount", "requestdeliveryby", "name")
    };
    query.Criteria.AddCondition("requestdeliveryby", ConditionOperator.LastXFiscalPeriods, 10);

    var entities = service.RetrieveMultiple(query).Entities.ToArray();
    PrintEntities(entities);
}

static void PrintEntities(Entity[] salesOrders)
{
    var total = 0m;
    foreach (var so in salesOrders)
    {
        Console.WriteLine($"Name: {so.GetAttributeValue<string>("name")} - Date: {so.GetAttributeValue<DateTime>("requestdeliveryby")} - Amount: {so.GetAttributeValue<Money>("totalamount")?.Value}");
        total += so.GetAttributeValue<Money>("totalamount")?.Value ?? 0m;
    }

    Console.WriteLine($"Total: {total}\n");
}

Based on the above code, if the Fiscal Year Settings is based on Monthly:

Fiscal Period Template Monthly

Here is the result:

Fiscal Year Settings Monthly Result

Based on the result above:

• ConditionOperator.InFiscalYear will extract the year of “requestdeliveryby” and filter by the string year that you passed (“2022”).
• ConditionOperator.InOrAfterFiscalPeriodAndYear can help you filter the month and year of “requestdeliveryby” (>= Month: “01” – January, Year: “2023”) and ConditionOperator.InOrBeforeFiscalPeriodAndYear can help you filter the month and year of “requestdeliveryby” (<= Month: “12” – December, Year: “2023”).
• ConditionOperator.InFiscalPeriod it will extract the month of “requestdeliveryby” and show the month which you passed (= “01” – January).
• ConditionOperator.LastXFiscalYears will extract the year of “requestdeliveryby” and show the last x years of the value you passed (2 meaning will pass 2024 and 2023).
• ConditionOperator.LastXFiscalPeriods will extract the month of “requestdeliveryby” and show the last x months of the value you passed (10 means April, March, Feb, January 2024, and December, November, October, September, Agust, July 2023).

If your Fiscal Period Template you set as Annually:

Fiscal Period Template Annually

Here is the result:

Fiscal Year Settings Annually Result

Based on the above result, the weird part is on the ConditionOperator.InFiscalPeriod where it will show all the records (I’m not sure the logic that makes all the data printed).

Summary

Based on all the “Fiscal” operators, you can utilize ConditionOperator.InOrAfterFiscalPeriodAndYear and ConditionOperator.InOrBeforeFiscalPeriodAndYear which is powerful for filtering the data based on the month and year. If you need to be based on the years, you can use ConditionOperator.InFiscalYear with combination Or and set the years that you want to query. Again, you also still can rely on the DateTime operator such as LessEqual or GreaterEqual, and pass the DateTime. But the purpose of this blog post is to show alternative ways to do it. 😎 PS, you also can read the post from Priyesh that has a similar topic here.

Happy CRM-ing!