Help with creating measure and building chart

[SM_SHED]

Hello,

I am trying to build a chart and i am struggling to find out wehy some measures aren't working.

I have 3 Measure: RunningTotalCurrentMonthRevenue, RunningTotalCurrentMonthRevenue2024, MonthlyForecast.

I would like to make a chart with the x axis as days of the current month and the y values as the 3 measures. below is the dax code for each measure and an image of the resultingchart. I cannot get the measures other than "RunningTotalCurrentMonthRevenue" to accumulate correctly over the month and instead just get a flat line.

RunningTotalCurrentMonthRevenue =

VAR CurrentMonth = MONTH(TODAY())

VAR CurrentYear = YEAR(TODAY())

RETURN

CALCULATE (

    SUM('Sales Query Live'[Selling Price]),

    FILTER (

        ALL('DateTable'),

        'DateTable'[Date] <= MAX('DateTable'[Date]) &&

        MONTH('DateTable'[Date]) = CurrentMonth &&

        YEAR('DateTable'[Date]) = CurrentYear

    )

)

-------

RunningTotalCurrentMonthRevenue2024 =

VAR CurrentMonth = MONTH(TODAY())

VAR CurrentYear = YEAR(TODAY())

VAR MaxDate = MAX('DateTable'[Date])

RETURN

CALCULATE (

    SUM('Sales Query Live'[Selling Price]),

    FILTER (

        ALL('DateTable'),

        'DateTable'[Date] <= MaxDate &&

        MONTH('DateTable'[Date]) = CurrentMonth &&

        YEAR('DateTable'[Date]) = CurrentYear - 1

    )

)

-------

MonthlyForecast =

VAR CurrentMonth = MONTH(TODAY())

VAR CurrentYear = YEAR(TODAY())

RETURN

CALCULATE (

    SUMX (

        'Sales Query Live',

        VAR IncreaseRate =

            SWITCH (

                TRUE(),

                'Sales Query Live'[SorMaster.Customer] IN {List of customers in the form  "customer1", "customer2", "customer3"}, 0.15,

                'Sales Query Live'[SorMaster.Customer] IN {"customer4", "cumtomer5"}, 0.35,

                0 -- Default case if no match

            )

        RETURN 'Sales Query Live'[Selling Price] * (1 + IncreaseRate)

    ),

    FILTER (

        ALL('DateTable'),

        'DateTable'[Date] <= MAX('DateTable'[Date]) &&

        MONTH('DateTable'[Date]) = CurrentMonth &&

        YEAR('DateTable'[Date]) = CurrentYear - 1

    )

)

--------

[DataNinja777 · Accepted solution]

Hi @SM_SHED ,

The issue is this: your RunningTotalCurrentMonthRevenue2024 and MonthlyForecast aren't actually "accumulating" day-by-day. Instead, they are summing the entire month's eligible records again and again, leading to a flat line. Basically, every single day, they were showing the full monthly value rather than progressively stacking up. No wonder they just laid there like a stunned fish.

To fix it, the measures need to accumulate properly across the days. Instead of checking whether the full date is less than the max date (which catches all kinds of irrelevant dates), we need to check the day number relative to the current day you're plotting on the X-axis.

Here's the corrected code for your RunningTotalCurrentMonthRevenue, although yours wasn't far off. Just tightening the filter slightly:

RunningTotalCurrentMonthRevenue =
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
RETURN
CALCULATE(
    SUM('Sales Query Live'[Selling Price]),
    FILTER(
        ALL('DateTable'[Date]),
        'DateTable'[Date] <= MAX('DateTable'[Date]) &&
        MONTH('DateTable'[Date]) = CurrentMonth &&
        YEAR('DateTable'[Date]) = CurrentYear
    )
)

Now here is the corrected RunningTotalCurrentMonthRevenue2024, where the real surgery happens. You need to accumulate by Day of month, not the whole date:

RunningTotalCurrentMonthRevenue2024 =
VAR CurrentMonth = MONTH(TODAY())
VAR PriorYear = YEAR(TODAY()) - 1
RETURN
CALCULATE(
    SUM('Sales Query Live'[Selling Price]),
    FILTER(
        ALL('DateTable'[Date]),
        DAY('DateTable'[Date]) <= DAY(MAX('DateTable'[Date])) &&
        MONTH('DateTable'[Date]) = CurrentMonth &&
        YEAR('DateTable'[Date]) = PriorYear
    )
)

And for the MonthlyForecast, which should also be a proper cumulative forecast over the days rather than a giant pre-cooked lump, you want this:

MonthlyForecast =
VAR CurrentMonth = MONTH(TODAY())
VAR PriorYear = YEAR(TODAY()) - 1
RETURN
CALCULATE(
    SUMX(
        'Sales Query Live',
        VAR IncreaseRate =
            SWITCH(
                TRUE(),
                'Sales Query Live'[SorMaster.Customer] IN {"customer1", "customer2", "customer3"}, 0.15,
                'Sales Query Live'[SorMaster.Customer] IN {"customer4", "customer5"}, 0.35,
                0
            )
        RETURN 'Sales Query Live'[Selling Price] * (1 + IncreaseRate)
    ),
    FILTER(
        ALL('DateTable'[Date]),
        DAY('DateTable'[Date]) <= DAY(MAX('DateTable'[Date])) &&
        MONTH('DateTable'[Date]) = CurrentMonth &&
        YEAR('DateTable'[Date]) = PriorYear
    )
)

Now, the real magic here is that by comparing DAY('DateTable'[Date]) <= DAY(MAX('DateTable'[Date])), you’re progressively accumulating just as you move through the calendar days, rather than stubbornly recalculating the entire month each time. No more flatlines. Your chart should now have three distinct curves gracefully growing through the month like they’re supposed to.

If you want to go even fancier later, we could dynamically annotate today’s progress versus forecast, or shade the gap area. But right now, this fix alone will give you the day-by-day build-up you were missing.

Best regards,