I'm trying to create a measure that will count a running total as a budget.

[Sergii22]

I'm trying to create a measure that will count a running total as a budget. The filter is the last date of the fact.
Example: Plan for every first day of the month, for the year (budget), fact by day (revenue).

Let's say there is a fact for the last sales date in February, for example, February 7th.

I need the measure to calculate the plan for January and February on February 7 without taking into account the time interval I have chosen. Even if I click on January, the measure should cumulatively calculate the budget for 2 months.

I wrote the following measure:

DatesYTDTotalSumBudget =

IF(

    HASONEVALUE('Calendar'[Date].[Month]),

CALCULATE(

    [TotalSumBudget],

        'Calendar'[Date]

    )

,

[TotalSumBudget]

)

In fact, this formula works well. But with a plan, I always get a budget depending on the month I click on (

Does anyone know how I can fix the formula?

[Sergii22 · Accepted solution]

I solved the problem myself. Everything turned out to be simple)

BudgetCum = CALCULATE(

            SUM('Plan'[Budget]),

            FILTER(

                ALL('DateTable'),

                'DateTable'[Date] <= [End Date]

            )

        )

[Greg_Deckler]

@Sergii22 See if this helps: Better Year to Date Total - Microsoft Power BI Community

[Sergii22]

I tried to make the measure as you recommend in your video to get the cumulative budget amount.
Here's what I got

BetterBudgetCum =

VAR _Date = MAX('Calendar'[Date].[Day])

VAR _Year = MAX('Calendar'[Date].[Year])

VAR _Table = FILTER(ALL('Plan'),[Date] <= _Date && _Year)

RETURN

SUMX(_Table,[Budget])

1. The first two variables, _Date and _Year, retrieve the maximum date and year from the "Date" column in the "Calendar" table.

2. The _Table variable is created by filtering the "Calendar" table to include only rows where the "Date" column is less than or equal to _Date and the year is equal to _Year.

3. Finally, the SUMX function is used to calculate the cumulative sum of the "Budget" column in the _Table variable.

But the formula did not work(Nothing is displayed. Where did I go wrong?

[Sergii22]

Я попытался сделать измерение, как вы рекомендуете в своем видео, чтобы получить сумму совокупного бюджета.

Вот что я получил:

Сумма бюджета =

VAR _Date = MAX ( 'Календарь' [Дата] . [День] )

VAR _Year = MAX ("Календарь" [Дата]. [День] . [Год] )

VAR _Table = ФИЛЬТР ( ВСЕ ( "План" ), [Дата] <= _Дата && _Год )

ВОЗВРАЩАТЬСЯ

СУММ ( _Таблица , [Бюджет] )

1. Первые две переменные, _Date и _Year, извлекают максимальную дату и год из столбца «День» в таблице « Календарь ».

2. Переменная _Table создается путем фильтрации таблицы « Календарь » для включения только строк, в которых столбец «Дата» меньше или равен _Date, а год равен _Year.

3. Наконец, функция SUMX используется для вычисления совокупной суммы столбца «Бюджет» в переменной _Table.

Я попытался сделать измерение, как вы рекомендуете в своем видео, чтобы получить сумму совокупного бюджета.
Вот что у меня получилось
Но формула не сработала(Ничего не отображается. Где я ошибся?

[Sergii22]

Thanks, I've watched your video. Lots of interesting information for me. I have written a new measure (see below), but have not solved the problem of independence of the funded budget from the selected time range. As soon as I click on any product in any month, I get a plan for that particular month. And I need to have a fixed savings plan between the first sale date and the last sale date.
Do you have any other suggestions?

Budget **bleep** =

CALCULATE(

    [TotalSumBudget],

    Fact[Date] >= MIN ( Fact[Date] ),

        Fact[Date] <= MAX (Fact[Date]))

TotalSumBudget =

CALCULATE(

    SUM('Budget'[Budget]),

    'Fact'[Date]

)