Forum Discussion

dmartiprev's avatar
dmartiprev
Frequent Visitor
3 years ago
Solved

Sum data from another table with different aggregation of time

I have two tables: DailyData and MonthlyData. 

 

In the DailyData table, I have columns: "Date", "YrMo" which is shows the year and month that the Date resides in, and I have a third column of data called "Sales".  

 

In the MonthlyData table I only have one column "YrMo".  I would like to sum all the Sales data from the DailyData table into the MonthlyData table by matching the YrMo column. 

 

I have tried to make this both as a column and as a measure and neither seems to work: 

MonthlySalesTotal = CALCULATE(SUM(DailyData[Sales]),DailyData[YrMo]=MonthlyData[YrMo]).
 
Power BI doesn't seem to recognize the MonthlyData[YrMo].  I am getting a message that says "This expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression"  there is only be one table called DailyData with a column called "YrMo" and only one table called MonthlyData with a column called "YrMo".  Although, it is true that within the DailyData table there are multiple rows with the same YrMo since all dates that are within one month of time will have the same YrMo by design.  I thought this is how i could use the calculation function to aggregate to monthly table but maybe this is the cause for the error?
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi dmartiprev ,

     

    Based on your description, I have creeated a simple sample:

    If you need a measure:

    Measure = CALCULATE(SUM(DailyData[Sales]),FILTER(ALL(DailyData),[YrMo]=MAX('MonthlyData'[YrMo])))

    Output:

    If you need a column:

    Column = SUMX(FILTER('DailyData',[YrMo]=EARLIER(MonthlyData[YrMo])),[Sales])

    Output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi dmartiprev ,

     

    Based on your description, I have creeated a simple sample:

    If you need a measure:

    Measure = CALCULATE(SUM(DailyData[Sales]),FILTER(ALL(DailyData),[YrMo]=MAX('MonthlyData'[YrMo])))

    Output:

    If you need a column:

    Column = SUMX(FILTER('DailyData',[YrMo]=EARLIER(MonthlyData[YrMo])),[Sales])

    Output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.