How to allocate monthly data into daily granularity?

[Anonymous]

Hi all,

I have some data such as monthly targets in a table in my data model. It's a simple table with two columns: date, monthly target.
i.e 

Date	Monthly Target
1/1/21	$500
1/2/21	$750

I have a matrix that tracks my daily sales and is therefore at a daily granularity. I would like to add an extra column in my matrix for 'daily target' which will divide the monthly target by the amount of days in the month. How can I achieve this?

Further to this, our targets are sometimes delayed and therefore we may have sales in a month with no target data yet. Is there a way that - in the absence of target data for a month, we use the average of the last 3 months until target data is available?

Appreciate the help, thank you!

[v-luwang-msft]

Hi  @Anonymous   ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

Best Regard

Lucien Wang

[v-luwang-msft]

HI @Anonymous ，

Pls try the following steps，

Step 1,import data,then  create a new column,dax is below:

day = DATEDIFF('Table'[Date].[Date],EOMONTH ( 'Table'[Date].[Date],0),DAY)+1

Step 2,create the following measure:

threemonth1 = CALCULATE(SUM('Table'[Monthly Target]),FILTER(ALL('Table'), 'Table'[Date]>= edate(MAX('Table'[Date]),-3)

&&'Table'[Date]<=edate(MAX('Table'[Date]),-1)))

Step 3,create the following measure:

threemonthday = CALCULATE(SUM('Table'[day]),FILTER(ALL('Table'), 'Table'[Date]>= edate(MAX('Table'[Date]),-3)

&&'Table'[Date]<=edate(MAX('Table'[Date]),-1)))

Step 4，create a new column,dax is following:

Daily Target = IF('Table'[Monthly Target]<>BLANK(),'Table'[Monthly Target]/'Table'[day],'Table'[threemonth1]/'Table'[threemonthday])

Finial you can see like this :

If my answer helps you, you can mark it as an answer, thank you!

Click   here to  download my pbix file if needed.

Best Regard

Lucien Wang

[amitchandak]

@Anonymous , I have blog for yearly. But you can convert that to monthly

#PowerBI Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-Convert-to/ba-p/1476400

#powerbi Distributing/Allocating the Yearly Target(Convert to Daily Target): https://community.powerbi.com/t5/Community-Blog/Distributing-Allocating-the-Yearly-Target-Convert-to-Daily/ba-p/1463290