Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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! 🙂
Solved! Go to Solution.
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.
Proud to be a Super User!
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
Proud to be a Super User!
@ryan_mayu , refer if this file can help
https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0
Thanks Ryan!
is the'result' table the date table?
When I add a column:
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")
Proud to be a Super User!
@amitchandak Unfortunately no.
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!
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.
Proud to be a Super User!
How would you go about this with more than 1 year of budget numbers?
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.
Proud to be a Super User!
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!
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?
Proud to be a Super User!
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
95 | |
76 | |
72 | |
65 |
User | Count |
---|---|
140 | |
109 | |
103 | |
82 | |
73 |