Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table that is Sales by date. It looks like this:
Day # of Year | Date | Month | Year | Purchaser | Orders |
1 | 1/1/2019 | 1 | 2019 | Dan | 1 |
2 | 1/2/2019 | 1 | 2019 | Dan | 2 |
3 | 1/3/2019 | 1 | 2019 | Dan | 3 |
1 | 1/1/2020 | 1 | 2020 | Dan | 4 |
2 | 1/2/2020 | 1 | 2020 | Dan | 5 |
3 | 1/3/2020 | 1 | 2020 |
My Measures are:
2019 Totals = Calculate(Sum(Sales[Order]),Sales[Year]=2019
2020 Totals = Calculate(Sum(Sales[Order]),Sales[Year]=2020
When I plot these measures I get the values distributed correctly.
However, 2020 (obviously) is missing date entries. I want to take the 2019 Total for each day and apply it to the dates missing in 2020. Using my previous example, the data would look like this:
Day # of Year | Date | Month | Year | Purchaser | Orders |
1 | 1/1/2019 | 1 | 2019 | Dan | 1 |
2 | 1/2/2019 | 1 | 2019 | Dan | 2 |
3 | 1/3/2019 | 1 | 2019 | Dan | 3 |
1 | 1/1/2020 | 1 | 2020 | Dan | 4 |
2 | 1/2/2020 | 1 | 2020 | Dan | 5 |
3 | 1/3/2020 | 1 | 2020 | Dan | 3 |
How do I strip just the Year off of my calculations so this works?
Solved! Go to Solution.
Hi @CEllinger
When you import data into Power BI first time, please add an index column, then create calcualted columns as below:
Purchaser2 =
IF (
[Purchaser]
= BLANK (),
CALCULATE (
MAX ( 'Table'[Purchaser] ),
FILTER (
'Table',
'Table'[Index]
= EARLIER ( 'Table'[Index] ) - 1
)
),
[Purchaser]
)
orders2 =
IF (
[Orders]
= BLANK (),
CALCULATE (
MAX ( 'Table'[Orders] ),
FILTER (
'Table',
'Table'[Month]
= EARLIER ( 'Table'[Month] )
&& 'Table'[Day]
= EARLIER ( 'Table'[Day] )
&& 'Table'[Year]
= EARLIER ( 'Table'[Year] ) - 1
)
),
[Orders]
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @CEllinger
When you import data into Power BI first time, please add an index column, then create calcualted columns as below:
Purchaser2 =
IF (
[Purchaser]
= BLANK (),
CALCULATE (
MAX ( 'Table'[Purchaser] ),
FILTER (
'Table',
'Table'[Index]
= EARLIER ( 'Table'[Index] ) - 1
)
),
[Purchaser]
)
orders2 =
IF (
[Orders]
= BLANK (),
CALCULATE (
MAX ( 'Table'[Orders] ),
FILTER (
'Table',
'Table'[Month]
= EARLIER ( 'Table'[Month] )
&& 'Table'[Day]
= EARLIER ( 'Table'[Day] )
&& 'Table'[Year]
= EARLIER ( 'Table'[Year] ) - 1
)
),
[Orders]
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |