Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Calculate Sales from start date

In PowerBi I have 3 tables:
First table is Data consisting of:
1. BusinessDay - Day of the sale
2. OperationType - which shows if it was sale or return
3. Name - of sale product
4. Department - in which department the product was sold.
5. Sum - the sale amount of product
6. Sales - which is a measure of sales 

The second table Calendar which consists of dates
The third table Open which consists of:
1. department name
2. Department open date

All of these tables are connected.

I need measure which calculates the sales of 2023, but only the comparable overlapping periods.
So for example if the department opened on 25.02.2022, it should show sales of 2023 from 25.02.2023
And if the the store is opened in 01.01.2020, it should show sales from 01.01.2023
I try using formula:

 

Sales 2023 Comparable = 
VAR OpeningDate = MIN('Open'[open date])
RETURN
CALCULATE(
    [SWS],
    YEAR(DataFromPOST[BusinessDay]) = 2023,
    DataFromPOST[BusinessDay] >= OpeningDate
)

 

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Anonymous 

     

    Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

     

    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.