Calculate SUM of dax measure

[san_jois]

Hi,

I have a data table named 'Dailydata' with one column as 'Quantity'

For use of Time intelligence functions, I have created a reference table 'dCalender' and connected it with 'Date' in 'Dailydata' table

I have used this to create following measures:

Total Sales=sum(Dailydata(Quantity))

Total Sales LY=Caluclate (Sum (Dailydata [Quantity], SAMEPERIODLASTYEAR (dCcalender[date]))

I have a date Slicer

The error:

i) When I select a date range in the slicer, I get incorrect total in Total Sales LY.

ii) Also if I select 29 Feb 2016 as a single date, I still get value for Total Sales LY representing that for 28 Feb 15

To work around for error (i), I changed the formula for Total Sales LY as:

Total sales LY Sumx = sumx(dCalender,CALCULATE(sum(Dailydata[Quantity]),SAMEPERIODLASTYEAR(dCalender[Date])))

It is giving correct result now.

However, error (ii) still persists..

My Question: Is there an error in my approach of comparing sales using time intelligence function? What is the best method to get the correct comparison then? Pls suggest..

Thanks.

[Greg_Deckler]

Well, Feb 29th didn't exist in 15 so that's why you are getting the 28th I would expect.

[san_jois]

Thanks for replying..

That's what is the question..I mean that if 29th didn't exist in 2015, then the value it should project shoul be (Blank) and not that for a date previous..

Hope it clarifies..

[Greg_Deckler]

Only thing I could think of would be to wrap your calculation in an IF statement that checks the date(s) coming in and if it is something wonky to return BLANK(), otherwise, do the calculation. Seems like you would only ever have a single case where this would happen, Feb 29th. 

http://dataap.org/blog/2017/04/30/dax-leap-year-in-power-bi/

[san_jois · Accepted solution]

Ok..thanks..

[v-huizhn-msft]

Hi @san_jois,

Have you resolved your issue? The solution  @Greg_Deckler posted is right. If you have, please mark the right or helpful reply as answer. So more people will benefit from here.

Best Regards,
Angelia