Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sergii22
Frequent Visitor

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

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?

 
1 ACCEPTED SOLUTION
Sergii22
Frequent Visitor

I solved the problem myself. Everything turned out to be simple)
 
BudgetCum = CALCULATE(
            SUM('Plan'[Budget]),
            FILTER(
                ALL('DateTable'),
                'DateTable'[Date] <= [End Date]
            )
        )

View solution in original post

5 REPLIES 5
Sergii22
Frequent Visitor

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

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

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

Сумма бюджета =
VAR _Date = MAX ( 'Календарь' [Дата] . [День] )
VAR _Year = MAX ("Календарь" [Дата]. [День] . [Год] )
VAR _Table = ФИЛЬТР ( ВСЕ ( "План" ), [Дата] <= _Дата && _Год )
ВОЗВРАЩАТЬСЯ
СУММ ( _Таблица , [Бюджет] )
  1. Первые две переменные, _Date и _Year, извлекают максимальную дату и год из столбца «День» в таблице « Календарь ».

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

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

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

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]
)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.