Sales in many periods

[Daniff]

Good afternoon, I need your help. imagine you have a customer sales table and a manager history table for those customers with customer start and end data. imagine a manager who has 2 periods with a client. How can I make a dynamic formula for him to provide me as sales of this manager in the periods of the history table? Thank you very much

 

[v-yanjiang-msft · Accepted solution]

Hi @Daniff ,

According to your description, here's my solution.

1. Create a date table, the date range should cover the dates that appear in the sample.

Date = CALENDAR(DATE(2022,1,1),DATE(2022,12,31))

2. Create a calculated column in the History Manager table.

End Column = IF('History Manager'[End Date]=BLANK(),TODAY(),'History Manager'[End Date])

3. Create a new table.

Table =
FILTER (
    GENERATE ( 'History Manager', 'Date' ),
    'Date'[Date] >= 'History Manager'[Start Date]
        && 'Date'[Date] <= 'History Manager'[End Column]
)

4. Create a measure.

Measure =
VAR _Client =
    SELECTCOLUMNS (
        FILTER (
            'History Manager',
            'History Manager'[Manager] = MAX ( 'History Manager'[Manager] )
        ),
        "Client", 'History Manager'[Client]
    )
VAR _T =
    FILTER (
        ALL ( 'Sell Out' ),
        'Sell Out'[Client]
            IN _Client
                && 'Sell Out'[Date]
                    IN SELECTCOLUMNS (
                        FILTER (
                            'Table',
                            'Table'[Client] IN SELECTCOLUMNS ( 'Sell Out', "Client", 'Sell Out'[Client] )
                        ),
                        "Date", 'Table'[Date]
                    )
    )
RETURN
    CALCULATE ( SUM ( 'Sell Out'[Sell Out] ), _T )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@Daniff ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

OR

SCD
Guyinacube - https://www.youtube.com/watch?v=tKeaQpWynzg

https://www.youtube.com/watch?v=E1ZABKBpkdg

https://www.zartis.com/scd-implementation-with-temporal-tables-in-power-bi/

[Daniff]

Hi, thank you very much for your reply. Attached I send the power bi project to be able to test. We have 2 tables. one with the history of clients by manager and respective periods. The second table is the customer sales with the date. The final result I want is a table per manager with the total sales (just add the sales in the period that each manager had the customer). Can you help? Thank you very much

Power BI - https://we.tl/t-unL4YALu3d

[Daniff]

Hello please help me