sum of values based on date ranges on repetetion of multipe time frames

[nagasaikumarb22]

Hi all,

I am Working for  a requirement in powerbi same schema mentioned in excel format  named in sheet 1. In sheet 1 we need calculation of summing up for date range not individually.In sheet 2 i had input data required for calculation.

please help me out in calculating for multi recurrent time frames.

[v-zhangti]

Hi, @nagasaikumarb22 

You can try the following methods.

Column:

Product Sold = 
CALCULATE (
    SUM ( Requirement[Products Sold] ),
    FILTER ( 'Data input', [State] = EARLIER ( 'Data input'[State] ) )
)

Total = 
CALCULATE (
    SUM ( Requirement[Total] ),
    FILTER ( Requirement, [State] = EARLIER ( 'Data input'[State] ) )
)

Single cost = DIVIDE([Total],[Product Sold])

Please see the attachment for details.

Best Regards,

Community Support Team _Charlotte

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

[nagasaikumarb22]

Hi,One thing missed out sir:

Year range is different could you recheck as per year range.And also sir,How date range is designed in data input?

2022 and 2023 ranges also mentioned over there

Thanks in advance

[v-zhangti · Accepted solution]

Hi, @nagasaikumarb22 

I have readjusted the data, please check it again.

Column:

Product Sold = 
IF (
    [State] = "TX",
    CALCULATE (
        SUM ( Requirement[Products Sold] ),
        FILTER (
            'Data input',
            [State] = EARLIER ( 'Data input'[State] )
                && [Date] >= "2021/1/1"
                && [Date] <= "2021/3/31"
        )
    ),
    IF (
        [State] = "FL",
        CALCULATE (
            SUM ( Requirement[Products Sold] ),
            FILTER (
                'Data input',
                [State] = EARLIER ( 'Data input'[State] )
                    && [Date] >= "2021/1/1"
                    && [Date] <= "2022/3/31"
            )
        ),
        IF (
            [State] = "CA",
            CALCULATE (
                SUM ( Requirement[Products Sold] ),
                FILTER (
                    'Data input',
                    [State] = EARLIER ( 'Data input'[State] )
                        && [Date] >= "2021/1/1"
                        && [Date] <= "2023/3/31"
                )
            )
        )
    )
)

Is this the result you want?

Best Regards,

Community Support Team _Charlotte

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

[nagasaikumarb22]

yes its clear.This code is staticly written, now my doubt Can we make it dynamic like if states are many how can we write many IF conditions?

[v-zhangti]

Hi, @nagasaikumarb22 

What is the format of your dynamic data? You can provide sample data for reference.

Best Regards

[moizsherwani]

@nagasaikumarb22 this is a PowerBI desktop forum so I think you might be better of asking this in an Excel formum.