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:    Month Value May 300 June 600 ... ...   I would like to divide the value of the month equally on every day, so for e...
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.

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

 

 

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

    amitchandak  Unfortunately no.

    ryan_mayu 

    I know why your approach didnt work now. 

     

    I have multiple values for each date with different ItemNumbers: 

    DateItem numberValue
    June 001600
    June 002300
    July.......

     

    So my result should look like: 

    DateItem number Value
    1 June 00120
    1 June 00210
    2 June 00120
    2 June00210
    .........

     

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

     

    Thank you! 

    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      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.

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

        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!