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

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! 

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

      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's avatar
        iiomarioii
        Icon for Helper II rankHelper II
        6 years ago

        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

         

         

         

  • AlexaderMilland's avatar
    AlexaderMilland
    Icon for Helper III rankHelper III
    4 years ago

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

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

      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.