Forum Discussion

pdbenbow's avatar
pdbenbow
Icon for Resolver II rankResolver II
6 years ago
Solved

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 suppo...
Need Help
  • pdbenbow's avatar
    pdbenbow
    6 years ago

    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.

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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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.

pdbenbow's avatar
pdbenbow
Icon for Resolver II rankResolver II
6 years ago

Anonymous, 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.

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.

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

  • adambhappy's avatar
    adambhappy
    Icon for Resolver II rankResolver II
    6 years ago

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

    • pdbenbow's avatar
      pdbenbow
      Icon for Resolver II rankResolver II
      6 years ago

      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's avatar
        pdbenbow
        Icon for Resolver II rankResolver II
        6 years ago

        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.

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