CY-PY calculation for a value within a column

user048760 ⚪️
edited April 14 in Card Building

The issue I am running in to is how to call out the value with in the column i want to find CY-PY variance. I am using the formula from Domo page on the topic. In that formula it only has use using a single column. How can I find that variance of one single value with in a column?

SUM(CASE WHEN YEAR(`Activity_Date`) = YEAR(CURRENT_DATE()) THEN `Value` = 'Occ % of Total Area' ELSE 0 END)

- SUM(CASE WHEN YEAR(`Activity_Date`) = YEAR(CURRENT_DATE()) - 1 THEN `Value` = 'Occ % of Total Area' ELSE 0 END)

This is using aggregate data. So I have end of month data historically, with current day. What I would want to is have a calculation I can use for multiple values all located in the value column. YOY - current day % change over same month LY. MTD. and Today's % changed compared to 2 months ago....April 14th compared to february. Attached is sample data.



  • GrantSmith
    GrantSmith Indiana 🔵

    Hi @user048760

    What you're wanting to do is possible by utilizing a date offset dimension. Essentially you'll have records for each day for current, lat week, last month, 2 months ago, whatever you want to define your offset / period definitions. There's been several write ups about this specific topic on the Dojo before. I've written on this topic here:

    Short version:

    • Create a list of dates (use the dates Domo dimension dataset) with offsets (I used a MySQL DataFlow )
    • Join your dataset to this offset table on the date field in your dataset and the comparison date in the offset dataset
    • Use the report date in your reports and filter the offset period type (last week, 2 months ago etc)

    This will allow you compare a single date to whatever time ago you define. You can use beast modes to define your differences over the different periods. For example -


    SUM(CASE WHEN `Offset` = 'Current' THEN `Value` ELSE 0 END) - SUM(CASE WHEN `Offset` = 'Last Year' THEN `Value` ELSE 0 END)


    SUM(CASE WHEN `Offset` = 'Current' THEN `Value` ELSE 0 END) - SUM(CASE WHEN `Offset` = 'Last Month' THEN `Value` ELSE 0 END)

    @jaeW_at_Onyx has a good video of this process as well:

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!