Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Syndicate_Admin
Administrator
Administrator

Cumulative curves in the fiscal calendar

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)

agromero90_2-1635119631344.png

agromero90_3-1635119655587.png

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

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

Hi @Syndicate_Admin ,

 

5.png

 

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.

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

agromero90_0-1635380711645.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.