Forum Discussion

Talvien's avatar
Talvien
Helper I
10 years ago
Solved

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.

 

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:

 

 

Thanks in advance!

Need Help

8 Replies

  • Talvien's avatar
    Talvien
    Helper I
    10 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      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

      • Talvien's avatar
        Talvien
        Helper I
        10 years ago

        Hi Anonymous,

         

        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!