Calculated Table + Add Columns (when filtered by category)

[THPNO80]

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)

[amitchandak · Accepted solution]

@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-...

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Tables 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

[THPNO80]

Hi @amitchandak 

Awesome! your article was extremely helpful to get a solution to this issue. Thank you very much 🙂