Forum Discussion

iiomarioii's avatar
iiomarioii
Icon for Helper II rankHelper II
6 years ago
Solved

Split monthly values to daily values

Hi guys,

 

this my problem:

I got the following table: 

 

MonthValue
May300
June600
......

 

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: 

DateValue
1 June20
2 June20
3 June 20 
......

for every month

 

Any solutions for this?

Thank you very much! 🙂

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    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.

12 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    6 years ago

    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

    • iiomarioii's avatar
      iiomarioii
      Icon for Helper II rankHelper II
      6 years ago

      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's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        6 years ago

        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")