Forum Discussion

rsbin's avatar
rsbin
Community Champion
4 years ago

Summarize Columns from two tables

Hello Folks,

Having difficulty with Summarize and/or Calculated Table.  I am trying to combine data from two tables:

1) DIM_Date_Tabular (Date Dimension Table) - Contains future dates to enable forecasting

2) HourlyStatisticsbyOperator (My Fact table)

 

 

= SUMMARIZE( FILTER( DIM_Date_Tabular, 
		         DIM_Date_Tabular[Date] <= Today() &&
		         DIM_Date_Tabular[Date] >= MIN(HourlyStatisticsByOperator[Date] )),
		         DIM_Date_Tabular[Date],
		         "WorkOrders", SUM(HourlyStatisticsByOperator[AllLiftWorkOrders] ))

 

 In my Fact Table, I have another column called "FacilityName".  I need to group my "WorkOrders" by FacilityName, so final result is as below:

DateFacilityNameWorkOrders
9/13/2021A0
9/13/2021B0
9/13/2021C0
9/13/2021D0
9/13/2021E0
9/12/2021A336
9/12/2021B316
9/12/2021C214
9/12/2021D345
9/12/2021E359
9/11/2021A123
9/11/2021B456
9/11/2021C789
9/11/2021D224
9/11/2021E315

Any assistance would be much appreciated!

 

Thanks in advance and Best Regards

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    rsbin , try like

     

    SUMMARIZE( HourlyStatisticsByOperator,HourlyStatisticsByOperator[FacilityName],
    DIM_Date_Tabular[Date],
    "WorkOrders", calculate(SUM(HourlyStatisticsByOperator[AllLiftWorkOrders] )FILTER( DIM_Date_Tabular,
    DIM_Date_Tabular[Date] <= Today() &&
    DIM_Date_Tabular[Date] >= MIN(HourlyStatisticsByOperator[Date] )))

    • rsbin's avatar
      rsbin
      Community Champion
      4 years ago

      Hi amitchandak,

      Thanks kindly for the prompt response.  Unfortunately, doesn't quite work the way I need it to.  I am not getting today's date in the table.  I'm guessing this is due to the fact Today's Date does not exist in my Fact Table.  Any suggestions on how to alter your solution?