Month Over Month Calculation

Winnie_1234
Winnie_1234 Member
in Beast Mode
Sample.png

Hi, looking for help to calculate the Month over Month (MoM) with the metrics and months as above.

I had successfully created my metrics by row, and Months by column in DOMO, however, I failed to create the calculated field for MoM Difference, and Mom % Difference as red in sample above.

starting with 01-May because May is the fiscal year of my employer, I have a variable set which i can select Gregorian, that works fine too, my info will show accordingly, but i couldn't figure out the MoM as well

Please help, appreciate it

Answers

  • Winnie_1234
    Winnie_1234 Member

    to further add on, 01-May, 02-Jun, 03-Jul is my way of arranging the month name in ascending format when i pick Financial Year in variable. if i switch to Gregorian Year in variable, it will show 01-Jan, 02-Feb, 03-Mar(month name) accordingly.

    i do have 2 different fields for Gregorian Date and Financial Date, i.e. 2023-01-01, 2025-01-01, just month and year info are available since my data is monthly data

    This is my current beast mode to calculate the values (accurate)

    CASE
    WHEN Calendar='Gregorian'
    THEN CASE WHEN Gregorian Year = Custom Year AND Trxn Version NOT IN (xxx)
    THEN -1*Monthly ELSE 0 END

    WHEN `Calendar`='Financial' 
    
	THEN CASE WHEN `Financial Year` = `Custom Year` AND `Trxn Version` NOT IN (xxx)
    
    THEN -1*`Monthly` ELSE 0 END

    END

  • DashboardDude
    DashboardDude Coach

    @Winnie_1234 so your column names are '01-May' Or is this a date column and it just changes depending on the month range you choose?

    Circle.png

    John Le

    Are you on my newsletter? If not, signup here so you don't miss out on my Domo tricks, alerts about my webinars, cooking tips and more

    Signup here:

    https://www.dashboarddudes.com/newsletter

  • Winnie_1234
    Winnie_1234 Member

    I have 2 variables, the first is Calendar type (Gregorian/Financial), and my second variable is Year (2025/2024/2023). The dataset is monthly data, so no date involved here, but I did prepared 2 extra fields (Gregorian Date & Financial Date), but they are just placeholder, meaning 2024-01-01, 2025-04-01 no exact date, thinking if I can use them in any of my calculations

    The 01-May, 02-Jun, 03-Jul until 12-Apr you see is my way of sorting my Month_Name by Financial Year. When i switch my Calendar Type to Gregorian, then you will see 01-Jan, 02-Feb, 03-Mar until 12-Dec. I created the number to sort my 2 Calendar Type in ascending order

    This is my current Actual_All calculated field, they seem to work perfectly. Just that I couldn't calculate the MoM (Difference & Percentage) after this step

    CASE
    WHEN Calendar='Gregorian'
    THEN CASE WHEN Gregorian Year = Custom Year AND Trxn Version NOT IN (ABC)
    THEN -1*Monthly ELSE 0 END

    WHEN `Calendar`='Financial' 
    
	THEN CASE WHEN `Financial Year` = `Custom Year` AND `Trxn Version` NOT IN (ABC)
    
    THEN -1*`Monthly` ELSE 0 END

    END

    Template.png

    By the way, John, you're my go-to-person on Internet to learn more about Domo, and how to use it. Thanks a lot, I benefited by watching a lot of your tutorial videos.

  • Manasi_Panov
    Manasi_Panov Contributor

    Hello @Winnie_1234,

    If you want to visualize the MoM bit not in a table, there is a convenient way to do it with the 'Variance Bar Line' chart:

    image.png

    To display this in a table, you'll need to modify your dataset to include a 'Previous Month Sales' column. This can be achieved during the ETL process using a Lag function or by grouping and appending the data. Once added, both fields can be included in a pivot table, and you can use Beast Mode to calculate the difference (Sales - Previous Month Sales) or the MoM growth (SUM(Sales) / SUM(Previous Month Sales)). Let me know if you'd like a detailed walkthrough of the ETL steps.

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • Winnie_1234
    Winnie_1234 Member

    Regarding on adding 1 extra field - Previous Month Sales in the ETL process, I stumbled upon this tutorial ytdy , is it this one? I am thinking this will be my last resort if I have no solution

    https://www.youtube.com/watch?v=oH0nUfVEsWA

    Hoping i can fix it just using calculated field, because I can do a similar calculation (a function in 1 click) in another BI tool when I build Table (sometimes management still prefer Table because it's informative and can cover more aspects), thought maybe Domo may have this feature that I might have missed

    Yes, the variance bar line does look like 1 good feature/method, but i probably can't use it because I have 40 over metrics to show in 1 chart/table. The 01-Sales, 02-Cost, the list goes on until 40 over. My initial plan in to build it in Table, then use formatting to colour rule those with alarming % change. But of course i welcome changes if this way cannot work

  • Manasi_Panov
    Manasi_Panov Contributor
    edited April 10

    Hi @Winnie_1234,

    Yes, this video is the method with the LAG function. The Group and Append method should look like this but you have to adapt it to your needs with month, not year. There are other approaches as well.

    image.png

    As far as I know, there is no way to do MoM/YoY in Beast mode if you want to use any Table chart AND you want to use Month/Year as a column.

    If you’ve got multiple metrics like Sales or Cost, I’d go with a variable and switch the whole dashboard based on a dropdown like this:

    image.png

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Categories