Forum Discussion
Calculated Table + Add Columns (when filtered by category)
Hi everyone,
I do not have the "Financial Estimate" on a daily level (monthly only). So I created a table (Fig. 01) to calculate the daily average based on the estimated value for the current month. It's working properly, however, I need to make sure this table of averages (DAILY FINANCIAL AVG - FIG.01) could be recalculated if the filter context is applied by its category.
It would be possible to nest it with the CALCULTATABLE function? Thanks in advance for your help!
Calculated table based on financial estimate:
TAB_AVG_FOR_FINANCIAL =
VAR _days = DAY(EOMONTH(TODAY(),0))
// Returns the last day of the current month
VAR _value = 0
// incremental factor
VAR _genTab = GENERATESERIES(1,_days)
// Returns a single column table containing the a sequence of days of the current month
VAR _avgTab = ADDCOLUMNS(_genTab,"AVG_IS",_value+SUMX(FILTER(_genTab,[Value]<=EARLIER([Value])),SUMX(FIN_EST,FIN_EST[AVGX_DAY])))
// Create a serie based on the Financial AVG (related table)
RETURN _avgTab
(fig.01)
THPNO80 , Based on what I got, see if these can help
Measure way
Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzMTables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2
2 Replies
- amitchandakSuper User
THPNO80 , Based on what I got, see if these can help
Measure way
Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzMTables way
https://amitchandak.mediumcom/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2- THPNO80Frequent Visitor
Hi amitchandak
Awesome! your article was extremely helpful to get a solution to this issue. Thank you very much 🙂