Forum Discussion

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
      7 years ago

      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.