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
pdbenbow
Resolver II
Resolver II

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

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
Community Support
Community Support

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.

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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

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

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

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))

 

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.