Split monthly values to daily values

[iiomarioii]

Hi guys,

this my problem:

I got the following table: 

Month	Value
May	300
June	600
...	...

I would like to divide the value of the month equally on every day, so for example: 

June: 600 / 30days = 20

Result should be like: 

Date	Value
1 June	20
2 June	20
3 June	20
...	...

for every month

Any solutions for this?

Thank you very much! 🙂

[ryan_mayu]

@iiomarioii 

I think you need to create a full date table , then create a column

Column = 
VAR monthvalue=LOOKUPVALUE('Table (2)'[value],'Table (2)'[month],FORMAT('result'[Date],"mmmm"))
var days=COUNTX(FILTER(result,month('result'[Date])=month(EARLIER('result'[Date]))),'result'[Date])
return monthvalue/days

[amitchandak]

@ryan_mayu , refer if this file can help

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

[iiomarioii]

Thanks Ryan!

is the'result' table the date table? 

When I add a column: 

Column =

VAR monthvalue=LOOKUPVALUE('DemandForecastEntries'[Volume],'DemandForecastEntries'[ForecastStartDate],FORMAT('Date'[Date],"mmmm"))

var days=COUNTX(FILTER('Date',month('Date'[Date])=month(EARLIER('Date'[Date]))),'Date'[Date])

return monthvalue/days

I am getting the following error: 

Function 'LOOKUPVALUE' does not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

'DemandForecastEntries'[Volume] = Value (Whole number) 

'DemandForecastEntries'[ForecastStartDate] = Date (e.g. June 2020) 

'Date'[Date] = Date in a full Date table

Thank you for your help

[ryan_mayu]

@iiomarioii 

What's the month value in your table?

Why I use FORMAT('Date'[Date],"mmmm"))? It's because your sample data was May, June. That transfer date type to long month name.

In my result table, I change the date to long month name , then lookup for May and June.

Please make sure these two columns are the same data type.

'DemandForecastEntries'[ForecastStartDate],FORMAT('Date'[Date],"mmmm")

[iiomarioii]

@amitchandak  Unfortunately no.

@ryan_mayu 

I know why your approach didnt work now. 

I have multiple values for each date with different ItemNumbers: 

Date	Item number	Value
June	001	600
June	002	300
July	....	...

So my result should look like: 

Date	Item number	Value
1 June	001	20
1 June	002	10
2 June	001	20
2 June	002	10
...	...	...

I think I just have to add a second criteria into LOOKUPVALUE? 

Thank you! 

[ryan_mayu · Accepted solution]

@iiomarioii 

Then this scenario will be much more complicated because the date needs to mutiply the product number.

my solution is a little complicated.

1. add month column into your datetime table

2. Then create a new table

new table = 
VAR TBL1=ADDCOLUMNS(SUMMARIZE(DATETIME,DATETIME[Date]),"_month",MAXX(FILTER('DATETIME','DATETIME'[Date]=EARLIER(DATETIME[Date])),'DATETIME'[Month]))
VAR TBL2=ADDCOLUMNS(SUMMARIZE('Table','Table'[Item],'Table'[Month],'Table'[VALUE]),"_month",MAXX(FILTER('Table','Table'[Month]=EARLIER('Table'[Month])),'Table'[Month]))
RETURN  NATURALLEFTOUTERJOIN(TBL1,TBL2)

3. create a new column in the new table.

average = 
var day= CALCULATE(DISTINCTCOUNT('new table'[Date]),FILTER('new table','new table'[_month]=EARLIER('new table'[_month])))
return 'new table'[VALUE]/day

I am not sure if this is the best solution for you. Let's see if anyone else can provide a better one.

Hope this is helpful.

[AlexaderMilland]

How would you go about this with more than 1 year of budget numbers?

[ryan_mayu]

@AlexaderMilland 

could you pls provide the sample data and expected output?

Since this post has been solved, it's better to create a new post for your question.

[iiomarioii]

Thank you Ryan. This was very helpful. I am still trying to understand the complete formula but it works actually.

But I am getting the right values just for one month: 

But I need the same average values for all other months: 

Do you have a solution for this? 

Thank you! 

[ryan_mayu]

@iiomarioii 

I created the DAX based on your sample data. I also tried put data for July or other months. It works as well.

What's the date format in your month value table? like January 2019, March 2020? Long month name + year?

[iiomarioii]

@ryan_mayu 

Hi Ryan, 

my date format  is long month  + year in the value table (e.g. June 2020). My "Month" column that I ve created in my date table has the same date format. (mmm YYYY)

Thank you 

iiomarioii

[iiomarioii]

I ve deleted everything and started from the beginning. Now it works!!

Thank you very much Ryan! 

You have been great help!

Best regards

iiomarioii