Forum Discussion
How to turn this value into a calculation?
Hello, I am trying to create a formula that takes a running total for one column plus a static for another, based on the month selected. I drafted the followed table: 'Estimated Sales' comes from one datasource and 'Actual Sales' comes from another different datasource -- I've already set relationships between the two datasources. It's the 'Running Sales' column that I'm trying to figure out; what this column is doing is taking the actual sales PLUS estimated sales for the months that haven't occured yet. For example, Apple Running Sales for January would be: January's Actual Sales that have occured + Feb's Estimated Sales + March's Estimated Sales. And February's would be: January's Actual Sales + Feb's Actual Sales + March's Estimated Sales
Please let me know if there is a DAX formula that can calculate this. I have been doing it manually thus far. Thanks!
Create a date table and mark it as a date table. Remove any relationships between the two fact tables you have, and create relationships from the date table to each fact table. You may also need to create a dimension table for apples and bananas and link that to each fact table too.
Once you have the correct relationships in place you can write a measure like
Running Sales = VAR MaxDate = MAX ( 'Date'[Date] ) VAR ActualSales = CALCULATE ( SUM ( 'Table1'[Actual Sales] ), DATESYTD ( 'Date'[Date] ) ) VAR EstimatedSales = CALCULATE ( SUM ( 'Table2'[Estimated Sales] ), 'Date'[Date] > MaxDate ) VAR Result = ActualSales + EstimatedSales RETURN Resultwhich will sum the correct amounts based on the date table. Put this measure in a matrix with the month column from your date table and it should be fine.
2 Replies
johnt75Super User
Create a date table and mark it as a date table. Remove any relationships between the two fact tables you have, and create relationships from the date table to each fact table. You may also need to create a dimension table for apples and bananas and link that to each fact table too.
Once you have the correct relationships in place you can write a measure like
Running Sales = VAR MaxDate = MAX ( 'Date'[Date] ) VAR ActualSales = CALCULATE ( SUM ( 'Table1'[Actual Sales] ), DATESYTD ( 'Date'[Date] ) ) VAR EstimatedSales = CALCULATE ( SUM ( 'Table2'[Estimated Sales] ), 'Date'[Date] > MaxDate ) VAR Result = ActualSales + EstimatedSales RETURN Resultwhich will sum the correct amounts based on the date table. Put this measure in a matrix with the month column from your date table and it should be fine.
Thank you!
