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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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