Forum Discussion

NMehta's avatar
NMehta
Frequent Visitor
6 years ago

IF Scenarios with multiply

Hi,

 

I have two tables - Sales Budget and Weekly Date

For a particular month sales budget, I want to split it in weeks and multiply by weightage for each week.

 

Example: Sales for July is $2,300,000. I have the weight allocation for weeks as week 1 - 25%, week2 - 25%, week3 - 30%, week4 - 10%, week5 - 10%

How can I create a measure which will allocate the budget sales and divide it into these weekly allocation for all the months.

 

Thanks,

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    NMehta , You can date table to have following Column

    Start Month = STARTOMONTH('Date'[Date])
    WeekDay = WEEKDAY([Date],2) //monday
    Start of Week = [Date] -[WeekDay]+1 //monday
    Month Week = QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1

     

    This will give you a month's week.

     

    Check if this allocation code can help

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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi NMehta,

    Any date fields existed in your table? If this is a case, you can use weeknum function and year function to extract the specific date range records and calculate the percent.

    Measure =
VAR currDate =
    MAX ( table[Date] )
VAR currWeek =
    CALCULATE (
        SUM ( table[Sales] ),
        FILTER (
            ALLSELECTED ( table ),
            YEAR ( [Date] ) = YEAR ( currDate )
                && WEEKNUM ( [date] ) = WEEKNUM ( currDate )
        ),
        VALUES ( table[Category] )
    )
VAR total =
    CALCULATE (
        SUM ( table[Sales] ),
        FILTER ( ALLSELECTED ( table ), YEAR ( [Date] ) = YEAR ( currDate ) ),
        VALUES ( table[Category] )
    )
RETURN
    DIVIDE ( currWeek, total, -1 )

    If the above formula not help and you are still confused about coding formula, please share some dummy data with a similar data structure to help us clarify the structure and test coding formula.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng

    • NMehta's avatar
      NMehta
      Frequent Visitor
      6 years ago

      Hi,

       

      I have Date table with week of year, week number, year-week etc.

      I have also created a new week table with week in month and weekly weightage for each week of year.

      However, my sales budget is at a monthly level where the date is last date of each month.

      Now the issue is all the formulas which I tried to allocate the monthly budget to week give me the weekly allocation just for the last week of month instead of distrbuting it across all weeks of the month.

      The main reason is the dates in sales budget table and the other tables! I am unable to upload the pbix file or insert any screenshots to explain the scenario properly. However I really need help with this issue.

      Thanks,

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI NMehta,

        In fact, we not need the raw data that probably include sensitive data/records.
        Can you please build some dummy data with similar data structures? It will help us clarify your requirements and test coding formula on these fake data.

        Regards,
        Xiaoxin Sheng