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
Talvien
Helper I
Helper I

Aggregate rows on months with DAX

I have below table. All costs are aggregated form a daily level, which means i have several cost elements every day. The related budget is assigned to every first day of the month.

 Unbenannt.JPG

The problem is, that the project, the table belongs to, started at the middle of the month and doesn't have cost elements every day. This is why the budget is 0 €, when I try to build a measure for the YTD budget with the following DAX formular:

BudgetSumTillNow = CALCULATE(SUM(Budgets[Budget]);FILTER(Actuals;Actuals[Costs]<>blank()))

This is simply because there are no costs that are directly assigned to the first of May, where the May budget is assigned to.

 

In my opinion I have to aggregate the budget on a monthly base before I can calculate the YTD budget for the current year.

How can I aggregate the budget on months in DAX or is there any other idea how to solve the problem?

 

This is the underlying data model:

 

Unbenannt.JPG

 

Thanks in advance!

1 ACCEPTED SOLUTION
Anonymous
Not applicable
8 REPLIES 8
Talvien
Helper I
Helper I

Push

@Talvien

 

You could try the following.

 

1. In the Budget Table create a column called BudgetDateKey and this should be a nwhole number  like YYYYMMDD.

2. Similarly create a column AcutalsDateKey in the Actuals table and this should be a whole number like YYYYMMDD.

3. I am assuming the DateInt in the calendar table is also of the same YYYYMMDD  format.

4. Join the tables using the BudgetDateKey with Dateint and ActualsDateKey with Dateint.

5. You can now use a measure called BudgetTillNow defined

     as CALCULATE( [Budget], DATESYTD( 'Calendar'[Date] ) )

6. Similarly you can define for AcutalsTillNow.

 

Checkit out and if it works please mark this as a solution and also give kudos.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Hi @CheenuSing,

 

thanks for your answer, I have some questions on your solution.

 

1. In the Tables Actuals, Budget and Calendar I have a column with Date (Datetyp: Date). Why isn't this enough relation between them?

2. What do you mean by "Join"? Join them in an additional table? Or join them in DAX?

3. Doesn't the CALCULATE need an additional operator? Something like CALCULATE( SUM( 'Budget'[Budget] ), DATESYTD( 'Calendar'[Date] ) )

 

I really appreciate your help!

@Talvien

 

What I meant by join was the relationship between the tables using Manage Relationship in Power BI.

 

Yes you can use the additional operator in CALCULATE.  If the Budget is a measure defined as sum('Budget'[BudgetAmount]), then you dont need to put SUM in the first parameter of CALCULATE.

 

Hope this calrifies.

 

Cheers

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

Thanks for your answers.

 

Since I don't need the costs on a daily base I deleted the relationships of the 'Calendar'[Date] and the Dates in Budget and Actuals.

I replaced it by a Period Table as described in the link from @Anonymous.

 

Now it is very simple to aggregate.

 

I also tried @CheenuSing solution, but it didn't realy work for me. I'm just asking me what happens if I need also the aggregation on a Date Level and I have both realtionships. The DateKey and the PeriodKey. Which one will the filter use?

 

Nevertheless the main problem is solved. My originally formular works with this datamodell now.

 

I just have an other problem which can be discussed in an other topic. My slizer with the month names of the Period table instead of Calendar table shows blank values again eventhough I used the transform-exclude-blank-transform-back workaround.

Anonymous
Not applicable

Ho do you aggregate Amount month by month?

Suppose Jan 10 , Feb 30, Mar 20, Apr 80, May 25,.... is displayed in my amount field. I want to do trend analysis based on month but at the end of each month the sum should be aggregated. Like Jan 10, Feb 40, Mar 60, Apr 140, May 165,.... How to achevie this logic? Please guide me if any DAX formula is available.

Talvien
Helper I
Helper I

I guess I need a combination of different expressions together with SUMMERIZECOLUMNS. But I'm struggeling.

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.

Top Solution Authors