Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
9 years ago

Sales Funnel

I'm trying to calculate projected sales for the current month, next month, and the month after that (3rd month))

 

I have TotalServices$ as a measure already calculated and each Sales Opportunity has a projected close date.  I need to use these projecte close dates to calculate the projected sales for this month, next month, and the month after. 

 

Any help is appreciated. 

 

I tried this

60DayFunnel$ = CALCULATE([TotalServices$], 'dpmgr vwJMWebSalesOpportunitiesProduction'[NextMonth Close]= "True")

 

But it's giving me everything before and up to Next month, not just Next Month. 

Need Help

5 Replies

      • parry2k's avatar
        parry2k
        Super User
        9 years ago

        sure, understood, no problem, can you just share the column names (or create dummy names from your main dataset) and put  dummy data and paste it here.

  • Funnelsflex's avatar
    Funnelsflex
    New Member
    6 months ago

    This is a classic DAX challenge—moving from a static "True/False" flag to a dynamic temporal calculation. From our perspective at Funnelsflex, where we architect these data flows into live dashboards, the issue usually stems from the filter context within the CALCULATE function.

    Using a hardcoded string like "True" for a date-based funnel is often too rigid because it doesn't account for the rolling nature of time. To get a precise 3-month projection without "bleeding" previous months into the total, you should leverage EDATE or DATESBETWEEN combined with a standard Calendar table.

    Here is a more robust technical approach to isolate those specific months:

    Current Month: CALCULATE([TotalServices$], FILTER(ALL('Calendar'), 'Calendar'[MonthOffset] = 0))

    Next Month (Projected): CALCULATE([TotalServices$], FILTER(ALL('Calendar'), 'Calendar'[MonthOffset] = 1))

    3rd Month: CALCULATE([TotalServices$], FILTER(ALL('Calendar'), 'Calendar'[MonthOffset] = 2))

    At , we typically recommend building a Month Offset column in your Date table. This allows you to project your funnel forward (+1, +2, etc.) regardless of what today's date is. It prevents the "everything before and up to" issue you're seeing, as it forces the calculation to look only at the specific integer assigned to that future month.

    Isolating these time buckets is critical because it allows you to see the "velocity" of your funnel rather than just a cumulative pile of opportunities. Hope this helps you clean up that projection!