Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
)
Hi, @AdilFS
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.
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |