Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello Power BI Community.
I would appreciate your help on the following issue.
I have a "prior year sales" measure that is not totaling correctly in a custom financial calendar hierarchy. However, my regular "sales" measure is totaling correctly.
Here is an example of the issue:
Division Name | Year Name | Quarter Name | Period Name | Week Name | Date | Sales Amount | Sales Amount PY |
West | 2023 | Q01 | P01 | W04 | 1/22/2023 |
| $100.00 |
|
|
|
|
| 1/23/2023 | $500.00 | $400.00 |
|
|
|
|
| 1/24/2023 | $400.00 | $350.00 |
|
|
|
|
| 1/25/2023 | $600.00 | $500.00 |
|
|
|
|
| 1/26/2023 | $200.00 | $200.00 |
|
|
|
|
| 1/27/2023 | $300.00 | $50.00 |
|
|
|
|
| Total | $2,000.00 | $100.00 |
As you can see above, the total on Sales Amount PY is $100.00, the row on 1/22/2023. It should be $1600.00. If I were to use a slicer to filter out 1/22/2023 the total would be $400.00, the value for 1/23/2023.
All slicers appear to be working normally and filter both Sales Amount and Sales Amount PY as desired.
The measures exist in an SSAS tabular database data set and are written in DAX. I connect to the dataset through an SSAS connection from Power BI Desktop.
The prior year sales calculation is designed to get the sales from the prior year for a week number and week day number. The week number and week day number are columns in the calendar table. The calendar table has a relationship with sales table on the calendar date = to the sales transaction date. The calendar table is marked as a date table.
Here is the formula for each measure:
Sales Amount
Sales Amount:= SUM('Revenue'[Sales_Amount])
Sales Amount PY
Sales Amount PY:=
VAR curWeekDay = MIN ('Calendar'[Week Day No])
VAR curWeek = MIN ('Calendar'[Week No])
VAR curYear = MIN ('Calendar'[Year No])
VAR prevYear = curYear - 1
VAR pyDate =
CALCULATE (
MIN ('Calendar'[Date]),
FILTER (
ALL('Calendar'),
'Calendar'[Year No] = prevYear &&
'Calendar'[Week No] = curWeek &&
'Calendar'[Week Day No] = curWeekDay
)
)
VAR result = SUMX(CALCULATETABLE('Calendar', 'Calendar'[Date] = pyDate), 'Sales'[Sales Amount])
RETURN result
I have also tried calculating the "result" in the following way
VAR result =
CALCULATE (
SUM('Sales'[Sales_Amount]),
FILTER (
ALL('Calendar'),
'Calendar'[Date] = pyDate
)
)
And this.
VAR result =
CALCULATE (
SUM('Sales'[Sales_Amount]),
FILTER (
ALL('Calendar'),
'Calendar'[Year No] = prevYear &&
'Calendar'[Week No] = curWeek &&
'Calendar'[Week Day No] = curWeekDay
)
I note that removing the ALL from the filter returns a blank result.
Solved! Go to Solution.
With special thanks from @tobiaseld, we came up with a solution.
On my own, I realized that it would be optimal to add a new column to my Calendar table "PY_Date". This column was then populated with the date that matched my criterial of previous year, current week, current day of the week. This made the DAX calculation easier and I initially got the results that I wanted. Here is that calculation using the new column:
Sales Amount Prior Year:=
VAR pyDate = CALENDAR(MIN('Calendar'[PY_Date]), MAX('Calendar'[PY_Date]))
VAR result = CALCULATE (SUM('Revenue'[Sales_Amount]), ALL('Calendar'), 'Calendar'[Date] IN pyDate)
RETURN result
This worked well but it did not work for non-contiguous dates. If I selected weeks 1, 2, 4 and omitted week 3 from my data hierarchy slicer. I would get a sum total that still included week 3.
@tobiaseld provided the bit to make it work for non-contiguous date. Here is modification:
Sales Amount Prior Year:=
VAR pyDate = VALUES('Calendar'[PY_Date]) // This equates to the data filtered out by the slicer
VAR result = CALCULATE( SUM('Revenue'[GL_Amount]), ALL('Calendar'), 'Calendar'[Date] IN pyDate )
RETURN result
With special thanks from @tobiaseld, we came up with a solution.
On my own, I realized that it would be optimal to add a new column to my Calendar table "PY_Date". This column was then populated with the date that matched my criterial of previous year, current week, current day of the week. This made the DAX calculation easier and I initially got the results that I wanted. Here is that calculation using the new column:
Sales Amount Prior Year:=
VAR pyDate = CALENDAR(MIN('Calendar'[PY_Date]), MAX('Calendar'[PY_Date]))
VAR result = CALCULATE (SUM('Revenue'[Sales_Amount]), ALL('Calendar'), 'Calendar'[Date] IN pyDate)
RETURN result
This worked well but it did not work for non-contiguous dates. If I selected weeks 1, 2, 4 and omitted week 3 from my data hierarchy slicer. I would get a sum total that still included week 3.
@tobiaseld provided the bit to make it work for non-contiguous date. Here is modification:
Sales Amount Prior Year:=
VAR pyDate = VALUES('Calendar'[PY_Date]) // This equates to the data filtered out by the slicer
VAR result = CALCULATE( SUM('Revenue'[GL_Amount]), ALL('Calendar'), 'Calendar'[Date] IN pyDate )
RETURN result
Hi Amitchandak.
Thank you for your reply. The formulas that you provide do not address my needs. However, I appreciate your comment because it helps me describe the problem better.
What I am looking for is a way to correlate a particular date by a date from the previous year related by the same week and day of the week. The week and day of the week are supplied by a custom calendar table.
For example: If I am looking for the coresponding week and day of the week for the date 2023-01-23. I would see that 2023-01-23 is the 2nd day of week 4 in 2023. So i need to find the 2nd day of week 4 in 2022, which is 2022-01-17. Additionally 2022-01-23 is the first day in week 5.
So my formula works on the date level. however it does not sum properly at a hierarchacal level as described in my original statement.
This matters because we have a custom 4-4-5 fiscal calendar that starts on January 1st. The date 01-23 is in different fiscal periods in each year.
@oscarw , In most such calculations the grand total will be last value.
If you have date or can create date with help from year, week ,etc, then try a measures like
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |