cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pdbenbow Regular Visitor
Regular Visitor

DAX Time Intelligence - last year's data summing incorrectly at the year level

I've spent the better half of today trying a variety of methods and looking through "The Definitive Guide to DAX," and I can't figure out what I'm doing wrong.

I have a Power BI report that is supported by SSAS Tabular. In the tabular model I have a measure named "SumAmount" which acts as the foundation for all other measures, and which makes sure that revenue and expenses offset each other (please excuse lack of pretty indentation):

 

 

SumAmount:= 
VAR RevenueAmt = SUMX('Operating Ledger', IF(LOOKUPVALUE(Account[Account Group], Account[Account Code], 'Operating Ledger'[Account Code] ) = "Revenue", 'Operating Ledger'[Transaction Amount] ) )
VAR ExpenseAmt = SUMX('Operating Ledger', IF(LOOKUPVALUE(Account[Account Group], Account[Account Code], 'Operating Ledger'[Account Code] ) <> "Revenue", 'Operating Ledger'[Transaction Amount] ) )
RETURN
CALCULATE( ExpenseAmt - RevenueAmt )

 

 

The next measure calculates actual expenses by filtering out line items that are meant for setting budgets:

 

 

Actual:= 
VAR ActualAmount = CALCULATE([SumAmount], 'Rule Class'[Rule Class Code] <> "BD01")
RETURN
ActualAmount

 

 

All the other measures in my model are working perfectly, except for THIS ONE... this needs to be fixed somehow:

 

 

Previous Year Actual:=
VAR Previous_Year = DATE(YEAR(MAX('Fiscal Date'[Fiscal Date])) - 1,  MONTH(MAX('Fiscal Date'[Fiscal Date])), DAY(MAX('Fiscal Date'[Fiscal Date])))
RETURN
CALCULATE( [Actual], FILTER(ALL('Fiscal Date'), 'Fiscal Date'[Fiscal Date] = Previous_Year))

 

 

The result is correct when aggregated at the month level, but not at the year level.  At the year level, the measure is just taking the latest month's value.  Instead, it should be summing up to $575,900.89, as shown by the YTD column next to it in this screenshot:

Capture20191028.PNG

 

I have tried using SAMEPERIODLASTYEAR, but it gives me the same number repeating. A screenshot of my data model is included for reference, with the Fiscal Date table circled in red.

Capture20191028_1.PNG

 
1 ACCEPTED SOLUTION

Accepted Solutions
pdbenbow Regular Visitor
Regular Visitor

Re: DAX Time Intelligence - last year's data summing incorrectly at the year level

I finally figured it out.  My hunch that it was caused by the leap year proved to be true.  In the screenshot below, you can see the correct numbers, plus two additional columns:

 

"test2" shows the results of using the original DATE(YEAR(MAX([Date]))-1,MONTH(MAX([Date])), DAY(MAX([Date]))) variable. You can see how the final date of February 2019 is calculating as "3/1/2019" because Feb 2020 has 29 days instead of the usual 28.

"test3" shows the results of a new modified measure that takes the 29th day of Feb into account.

 

Thanks to everyone who tried to help.  This just took a while for me to figure out.

Capture20191030_3.PNG

Here's the final DAX for the "Previous Year Actual" and "Previous Year Actual YTD" measures:

Previous Year Actual:=
VAR LastDayOfMonth = IF(DAY(MAX('Fiscal Date'[Fiscal Date]))=29,DAY(MAX('Fiscal Date'[Fiscal Date]))-1,DAY(MAX('Fiscal Date'[Fiscal Date])))
VAR Previous_Year = DATE(YEAR(MAX('Fiscal Date'[Fiscal Date])) - 1,  MONTH(MAX('Fiscal Date'[Fiscal Date])), LastDayOfMonth )
return
IF(HASONEVALUE('Fiscal Period'[Month]), CALCULATE( [Actual], FILTER(ALL('Fiscal Date'), 'Fiscal Date'[Fiscal Date] = Previous_Year)), CALCULATE( [Actual YTD], FILTER(ALL('Fiscal Date'), 'Fiscal Date'[Fiscal Date] = Previous_Year)))

Previous Year Actual YTD:=
VAR LastDayOfMonth = IF(DAY(MAX('Fiscal Date'[Fiscal Date]))=29,DAY(MAX('Fiscal Date'[Fiscal Date]))-1,DAY(MAX('Fiscal Date'[Fiscal Date])))
VAR Previous_Year = DATE(YEAR(MAX('Fiscal Date'[Fiscal Date])) - 1,  MONTH(MAX('Fiscal Date'[Fiscal Date])), LastDayOfMonth )
RETURN
CALCULATE( [Actual YTD], FILTER(ALL('Fiscal Date'), 'Fiscal Date'[Fiscal Date] = Previous_Year))

 

View solution in original post

5 REPLIES 5
v-jayw-msft Senior Member
Senior Member

Re: DAX Time Intelligence - last year's data summing incorrectly at the year level

hi @pdbenbow ,

 

Here's a simple test I did according to your description:

 

Measure =
var sv = SELECTEDVALUE('Table'[Product],BLANK())
var ord = CALCULATE(MAX(sales[Order #]),FILTER(sales,sales[Product] = sv))
return
CALCULATE(SUM(sales[Revenue]),FILTER(sales,sales[Order #] = ord))
 
Measure 2 = SUMX(VALUES(sales[Order-Concat]),[Measure])
 
Measure 3 = ISFILTERED(sales[Order-Concat])
1.PNG
 

If this doesn't solve you problem, kindly share your sample data and excepted result to me if you don't have any Confidential Information.

Please upload your files to One Drive and share the link here.

 

Best Regards

 

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
pdbenbow Regular Visitor
Regular Visitor

Re: DAX Time Intelligence - last year's data summing incorrectly at the year level

@v-jayw-msft, thank you for your proposed solution.

I was actually able to get a better result by using HASONEVALUE. My updated DAX is below.

Previous Year Actual:=
VAR Previous_Year = DATE(YEAR(MAX('Fiscal Date'[Fiscal Date])) - 1,  MONTH(MAX('Fiscal Date'[Fiscal Date])), DAY(MAX('Fiscal Date'[Fiscal Date])))
RETURN
IF(HASONEVALUE('Fiscal Period'[Month]), CALCULATE( [Actual], FILTER(ALL('Fiscal Date'), 'Fiscal Date'[Fiscal Date] = Previous_Year)), CALCULATE( [Actual YTD], FILTER(ALL('Fiscal Date'), 'Fiscal Date'[Fiscal Date] = Previous_Year)))

However, I have noticed another issue.  Somehow, my "Previous Year Actual" measure is getting repeated for the months of February and March.

First screenshot (fiscal year 2020): February and March are showing as the same amount.

Capture20191030.PNG

Going back to fiscal year 2019 using my filters pane, the actuals for February and March are different.  In the screenshot above, it is obvious that February's "Previous Year Actual" should be $110,335.34, but instead it is somehow restating the March amount.

Capture20191030_1.PNG

I wonder... could this be caused by February 2020 having 29 days instead of 28? Is this a leap year issue? 

adambhappy Regular Visitor
Regular Visitor

Re: DAX Time Intelligence - last year's data summing incorrectly at the year level

Is there a reason you are using MAX() instead of LASTDATE()?

pdbenbow Regular Visitor
Regular Visitor

Re: DAX Time Intelligence - last year's data summing incorrectly at the year level

@adambhappy , no reason other than it was one specific technique I learned on this forum.  I'm open to other suggestions if they resolve this weird issue with my 2020 February and March numbers.

pdbenbow Regular Visitor
Regular Visitor

Re: DAX Time Intelligence - last year's data summing incorrectly at the year level

I finally figured it out.  My hunch that it was caused by the leap year proved to be true.  In the screenshot below, you can see the correct numbers, plus two additional columns:

 

"test2" shows the results of using the original DATE(YEAR(MAX([Date]))-1,MONTH(MAX([Date])), DAY(MAX([Date]))) variable. You can see how the final date of February 2019 is calculating as "3/1/2019" because Feb 2020 has 29 days instead of the usual 28.

"test3" shows the results of a new modified measure that takes the 29th day of Feb into account.

 

Thanks to everyone who tried to help.  This just took a while for me to figure out.

Capture20191030_3.PNG

Here's the final DAX for the "Previous Year Actual" and "Previous Year Actual YTD" measures:

Previous Year Actual:=
VAR LastDayOfMonth = IF(DAY(MAX('Fiscal Date'[Fiscal Date]))=29,DAY(MAX('Fiscal Date'[Fiscal Date]))-1,DAY(MAX('Fiscal Date'[Fiscal Date])))
VAR Previous_Year = DATE(YEAR(MAX('Fiscal Date'[Fiscal Date])) - 1,  MONTH(MAX('Fiscal Date'[Fiscal Date])), LastDayOfMonth )
return
IF(HASONEVALUE('Fiscal Period'[Month]), CALCULATE( [Actual], FILTER(ALL('Fiscal Date'), 'Fiscal Date'[Fiscal Date] = Previous_Year)), CALCULATE( [Actual YTD], FILTER(ALL('Fiscal Date'), 'Fiscal Date'[Fiscal Date] = Previous_Year)))

Previous Year Actual YTD:=
VAR LastDayOfMonth = IF(DAY(MAX('Fiscal Date'[Fiscal Date]))=29,DAY(MAX('Fiscal Date'[Fiscal Date]))-1,DAY(MAX('Fiscal Date'[Fiscal Date])))
VAR Previous_Year = DATE(YEAR(MAX('Fiscal Date'[Fiscal Date])) - 1,  MONTH(MAX('Fiscal Date'[Fiscal Date])), LastDayOfMonth )
RETURN
CALCULATE( [Actual YTD], FILTER(ALL('Fiscal Date'), 'Fiscal Date'[Fiscal Date] = Previous_Year))

 

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors