Forum Discussion

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

Calculating amount given for period - from one row

Hi,

I have the following table, containing an amount per day in the following format:

item (amount_per_day regards particular item)

start_date

end_date

include_holidays (true/false), calendar with holidays marked in a separate table

recurring (true/false)

recuring period (week, two weeks, month)

recurring end date (last date of start_date if recurring).

amount_per_day - the value of items for each day between start and end_dates, with the condition for holiday

 

My goal is to create a measure, let's say "Sum of amount", which could be used in calendar-based visuals and aggregated per month, quarters etc.. It should correctly calculate sums for periods spanning through the month boundaries.

 

How would you create such a measure? I thought about a crossjoin of such a table with the calendar table and then operating with

calculate and filters, but maybe it isn't the only/best approach?

 

Best,

R.

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi rylach ,

     

    We can use the following steps to meet your requirement.

     

    1. Create a date table first.

     

    Date = CALENDARAUTO()

     

     

    2. Then create four columns in Table to calculate the start date and end date.

     

    Next_start_week = [start_date]+7
    Next_end_week = [end_date]+7
    R_start_week = [Next_start_week]+7
    R_end_week = [Next_end_week]+7

     

     

    3. At last we can create a calculate table and create a table visual to get the result,

     

    Table 2 = 
VAR x =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[start_date] <= 'Date'[Date]
                && 'Table'[end_date] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
VAR y =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[Next_start_week] <= 'Date'[Date]
                && 'Table'[Next_end_week] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
VAR z =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[R_start_week] <= 'Date'[Date]
                && 'Table'[R_end_week] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
RETURN
ADDCOLUMNS ( UNION ( x, y, z ), "month", FORMAT ( [Date], "mmmm" ) )

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

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

    rylach If you can paste sample data, relationship between table and the expected output, it will be easier to provide the solution. 

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi rylach ,

     

    We suggest you can create a month column and quarter column, then add two slicers to use them.

    And put the following measure to your context.

     

    SUM_ =
CALCULATE ( SUM ( 'Table'[amount_per_day] ) )

     

    If it doesn’t meet your requirement, could you please provide a mockup sample  based on fake data or describe the fields of each tables and the relations between tables simply?

     

    It will be helpful if you can show us the exact expected result based on the tables.

     

    Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

      Hi,

      Unfortunately, this is not that simple 😉

       

      Look at the example record in the 'amounts' table:

      itemstart_dateend_dateinclude_holidaysrecurringrecurring periodrecurring_end_dateamount per day
      shoes2020-06-222020-06-24falseYESWEEK2020-07-0710

       

      This record expands to quantities:

       

      First period:

      22.06 - 10

      23.06 - 10

      24.06 - 10

       

      Second period (because we have weekly reccurence AND week after start_date is before recurring_end_date)

      29.06 - 10

      30.06 - 10

      01.07 - 10

       

      3rd period (because we have weekly reccurence AND another week after start_date is before recurring_end_date)

       

      06.07 - 10

      07.07 - 10

      08.07 - 10

       

      So, the measure should return, when used with monthly aggregation:

      June - 50

      July -  40

       

      I hope I explained it well 😉

       

      I could do this by:
      1. crossjoin with calendar table (multiplies this record for every day)

      2. calculatetable, filters - filtering out days outside of ranges indicated by date columns.

       

      Does it make sense or there is some better approach?

       

      Best,

      R.

       

      • v-zhenbw-msft's avatar
        v-zhenbw-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi rylach ,

         

        We can use the following steps to meet your requirement.

         

        1. Create a date table first.

         

        Date = CALENDARAUTO()

         

         

        2. Then create four columns in Table to calculate the start date and end date.

         

        Next_start_week = [start_date]+7
        Next_end_week = [end_date]+7
        R_start_week = [Next_start_week]+7
        R_end_week = [Next_end_week]+7

         

         

        3. At last we can create a calculate table and create a table visual to get the result,

         

        Table 2 = 
VAR x =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[start_date] <= 'Date'[Date]
                && 'Table'[end_date] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
VAR y =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[Next_start_week] <= 'Date'[Date]
                && 'Table'[Next_end_week] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
VAR z =
    SUMMARIZE (
        FILTER (
            CROSSJOIN ( 'Date', 'Table' ),
            'Table'[R_start_week] <= 'Date'[Date]
                && 'Table'[R_end_week] >= 'Date'[Date]
        ),
        'Table'[item],
        [Date],
        'Table'[amount per day]
    )
RETURN
ADDCOLUMNS ( UNION ( x, y, z ), "month", FORMAT ( [Date], "mmmm" ) )

         

         

         

        If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

        BTW, pbix as attached.

         

        Best regards,

         

        Community Support Team _ zhenbw

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.