YTD and PYTD issue, potentially using the EDATE function somehow?

DAX DAX Calculations
1

Hi,

I have columns of totals for YTD and Previous YTD figures in my table. I get correct numbers for the current month for the first few days in January. I get the totals for the entire month on all the previous time calculations, but the formulas do not take into account the partial days in the current month. Here are the formulas that I’m currently using:

1. YTD = CALCULATE(DISTINCTCOUNT(lwmain[lwmainid]),DATESYTD(MasterDate[Date]))

2. PYTD = CALCULATE(DISTINCTCOUNT(lwmain[lwmainid]), DATEADD(DATESYTD(MasterDate[Date]),-1,YEAR))

3. MTD = CALCULATE(DISTINCTCOUNT(lwmain[lwmainid]),DATESMTD(MasterDate[Date]))

4. PMTD = CALCULATE(DISTINCTCOUNT(lwmain[lwmainid]),DATEADD(DATESMTD(MasterDate[Date]), -1,YEAR))

I would really appreciate your help on this.

2

A demo model would certainly help on this one as it’s not super clear if you are calculating these as measures or calculated columns.

You should definitely be using measures by the way for any time intelligence calculations.

The DATESBETWEEN and EDATE functions should help you on this. You could try using:

LYMTD =
CALCULATE(DISTINCTCOUNT(lwmain[lwmainid]),
           DATESBETWEEN(MasterDate[Date],
           EDATE(STARTOFMONTH(MasterDate[Date]),-12),  EDATE(TODAY(),-12)))

Just try this and see how you go.

For more on time intelligence functions and more see below.

http://portal.enterprisedna.co/courses/mastering-dax-calculations/lectures/2000648

This is in the Mastering DAX Calculations course at Enterprise DNA Online.

image
image371×680 27.7 KB

Thanks
Sam