Problem with 12month cumulative

[AndrejZitnay]

Hello all,

I have bit of problem with 12 month cumulative.

If I use monthly breakdown than figures are right. Problem is with 12 month cumulative.

For some reason only two months doesn't match and it doesn't make sense to me.

Out of 12 months cumulative form Jan-16 to Sep-17 only Feb-17 & Sep-17 are out of sync.

Can you redirect me to some solution?

Date:

MonthYear = FORMAT([Date],"mmm")&"-"&FORMAT([Date],"yy")

MasterCalendar = CALENDAR(MIN('Date Table'[COMPLETIONDATE]),MAX('Date Table'[COMPLETIONDATE]))

Values :

12 Month Settlements = CALCULATE([Settlements],DATESBETWEEN(MasterCalendar[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(MasterCalendar[Date]))),LASTDATE(MasterCalendar[Date])))

Settlements = CALCULATE(COUNTROWS(Terminations),FILTER(Terminations,Terminations[Type_1]="Settlements"))

Many thanks.

Kind reards.

Andrej

[v-sihou-msft]

@AndrejZitnay

I can't reproduced your issue. And your formula looks good to me. What are the results on  Feb-17 and Sep-17? 

Can you share your .pbix for test?

Regards,

[AndrejZitnay]

Hello @v-sihou-msft

Unfortunately I can't share much however I have found something about why this two months are out of sync.

Difference in my 12 months total for September is 236

I find out that for some reason my formula picked up data for 09/30/16 where it shoudn't

In Sep-17 12 months total should have data from Oct-16 to Sep-17

In Feb-17 12 month total should have data from March-16 to Feb-17 

I can see that my formula picked up here as well data from 29/02/2016.

It could be case that we have 29 days in February only once every 4 years?

12 Month Settlements = CALCULATE([Settlements],DATESBETWEEN(MasterCalendar[Date],NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(MasterCalendar[Date]))),LASTDATE(MasterCalendar[Date]))) 

I am not sure if my formula look for exact day or day within month and than calculate 12 month total.

Can you help?

thx

Andrej

[Vvelarde · Accepted solution]

@AndrejZitnay

Hi, try adding EndofMonth in your measure:

12 Month Settlements V2 =
CALCULATE (
    SUM ( Hoja1[Settlements] ),
    DATESBETWEEN (
        MasterCalendar[Date],
        NEXTDAY (
            ENDOFMONTH ( SAMEPERIODLASTYEAR ( LASTDATE ( MasterCalendar[Date] ) ) )
        ),
        LASTDATE ( MasterCalendar[Date] )
    )
)

Regards

Victor

Lima - Peru

[AndrejZitnay]

Hello @Vvelarde,

That works for me.

Thank you very much for your help.

Much appreciated.

Kind regards.

Andrej