Cumulative curves in the fiscal calendar

[Syndicate_Admin]

Hello community!

I have a measure that counts the records in my transactional table and accumulates them to generate a curve. The problem is that I need the curve to only show the cumulative until the last filtered date of my transactional table (ENROLLMENT)

My measurement is as follows:

XNE Accumulated 202101 =

CALCULATE (

IF (

HASONEVALUE ( CALENDARIO[YEAR INTAKE 01] ),

VAR FechaActual =

MAX ( CALENDAR[DATE] )

VAR YearIntake =

VALUES ( CALENDARIO[YEAR INTAKE 01] )

RETURN

CALCULATE (

[NE],

FILTER (

ALL ( CALENDAR ),

CALENDAR[DATE] <= Current Date

&& CALENDARIO[YEAR INTAKE 01] = YearIntake

),

ALL ( CALENDAR ),

INTAKE[INTAKE] = "202101"

),

BLANK ()

),

DATEADD ( CALENDAR[DATE], -1, YEAR )

)

This measure generates the accumulated in all my fiscal year (March to April), and accumulates from the first date that the transactional table appears (ENROLLMENT) and ends on the last day of the fiscal year (31/03)

What I need is for the accumulated to end up in the last record of the filtered transactional table (the table affected by the filter context). In the case of the example that ends on 27/03, but taking into account that it must conform to what you have in the filter context.

I attach the data model:

https://educorpperu-my.sharepoint.com/:u:/g/personal/agustin_romero_upc_pe/EVjvZq6FVwZMliTaSDJoQJ8BV...

I would greatly appreciate your help!

Best regards!

Agustin Romero

[Anonymous]

Hi @Syndicate_Admin ,

I put [NE] in and found that 6/5/2021 is 23 instead of 1. What is the logic of your formula?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Thank you for your reply.

The measure [NE] is the account of the translational table 'ENROLLMENT'.

the difference is because the cumulative xne measure 202101 has a calculate() modifier that shows the year before the same date