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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.