Forum Discussion

Talvien's avatar
Talvien
Helper I
10 years ago

Push

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!

      • Talvien's avatar
        Talvien
        Helper I
        10 years ago

        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 Anonymous 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's avatar
      Anonymous
      Not applicable
      10 years ago

      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