Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
oscarw
Frequent Visitor

Prior year measure not totaling correctly in date hierarchy.

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.

 

1 ACCEPTED SOLUTION
oscarw
Frequent Visitor

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

 

View solution in original post

3 REPLIES 3
oscarw
Frequent Visitor

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

 

oscarw
Frequent Visitor

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. 

Fiscal week example.PNG

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. 

amitchandak
Super User
Super User

@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...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.