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
Riedwaan
New Member

Cumulative function embedded in a previous period function

Hi All,

 

I created four measure and need help with the last one.

 

1. Simple measure to count admissions:

Admissions_Current_Period = count(Events[EVENT_NO])

 

2. Measure to calculate the cumulative running total:

Admissions_Current_Period_Cumulative = sumx(FILTER(ALLSELECTED('events'),[ADMIT_DT] <=MAX([ADMIT_DT])),[Admissions_Current_Period])

 

3. Measure to get admissions for previous period:

Admissions_Previous_Period = 
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE([Admissions_Current_Period],FILTER(ALL('Date'[Date]),DATEADD('Date'[Date],+1,YEAR) >= MinDate
&& DATEADD('Date'[Date],+1,YEAR) <= MaxDate)
)

4. Measure to get the cumulative admissions for previous period:

Admissions_Previous_Period_Cumulative = 
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE( sumx(FILTER(ALLSELECTED('events'),[ADMIT_DT] <=MAX([ADMIT_DT])),[Admissions_Current_Period]),FILTER(ALL('Date'[Date]),DATEADD('Date'[Date],+1,YEAR) >= MinDate
&& DATEADD('Date'[Date],+1,YEAR) <= MaxDate)
)

 

Measure 4 is just adapting measure 2, to get cumulative, but i am obvious doing smething wrong?

Please can someone advise why my measure wont work?

 

thanks 

 

Riedwaan  

1 ACCEPTED SOLUTION

Hi @Riedwaan

 

Try this formula please. You can check it out in this file: https://drive.google.com/open?id=1ixcXeBkJtm-McpxPRNX-tzaLYRHXx-p2

 

Admissions_Previous_Period_Cumulative 2 =
VAR startdate =
    CALCULATE ( MIN ( 'Events'[ADMIT_DT] ), ALL ( 'Date' ) )
VAR enddate =
    LASTDATE ( DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
    IF (
        enddate < startdate,
        BLANK (),
        CALCULATE (
            [Admissions_Current_Period],
            DATESBETWEEN ( 'Date'[Date], startdate, enddate )
        )
    )

Cumulative function  embedded in a previous period function.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

Hi @Riedwaan,

 

DAX formula will response its context. Your formulas look great. A few questions.

1. Does the forth formula work? Or just returns wrong result?

2. What's its context? How did you create the report visual?

3. Can you share the .pbix file?

One suggestion: you can use SAMEPERIODLASTYEAR instead of the second filter.

Admissions_Previous_Period_Cumulative =
VAR MinDate =
    MIN ( 'Date'[Date] )
VAR MaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUMX (
            FILTER ( ALLSELECTED ( 'events' ), [ADMIT_DT] <= MAX ( [ADMIT_DT] ) ),
            [Admissions_Current_Period]
        ),
        SAMEPERIODLASTYEAR ( 'Date'[Date] )
    )

Best Regards!

Dale

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

Hi @v-jiascu-msft

 

Thanks for you response!

 

the fourth formula works but does not return the cumulative value, or the monthly value, theferefore just micmicking formular 3s answer.

 

I tried your formular, but returns the same answer

 

Attached is the pbix, trust you can set me straight..

 

https://www.dropbox.com/s/y2zw2vk2160u7fc/LOS%20Model.pbix?dl=0

 

 

thanks

 

Riedwaan 

Hi @Riedwaan

 

Try this formula please. You can check it out in this file: https://drive.google.com/open?id=1ixcXeBkJtm-McpxPRNX-tzaLYRHXx-p2

 

Admissions_Previous_Period_Cumulative 2 =
VAR startdate =
    CALCULATE ( MIN ( 'Events'[ADMIT_DT] ), ALL ( 'Date' ) )
VAR enddate =
    LASTDATE ( DATEADD ( 'Date'[Date], -1, YEAR ) )
RETURN
    IF (
        enddate < startdate,
        BLANK (),
        CALCULATE (
            [Admissions_Current_Period],
            DATESBETWEEN ( 'Date'[Date], startdate, enddate )
        )
    )

Cumulative function  embedded in a previous period function.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

@v-jiascu-msft Excellent, thanks!

 

 

@v-jiascu-msft

I was wondering, can you look at my forum issue and help :-). You seem to be the guru around here!

Riedwaan
New Member

Hi All,

 

I created four measure and need help with the last one.

 

1. Simple measure to count admissions:

Admissions_Current_Period = count(Events[EVENT_NO])

 

2. Measure to calculate the cumulative running total:

Admissions_Current_Period_Cumulative = sumx(FILTER(ALLSELECTED('events'),[ADMIT_DT] <=MAX([ADMIT_DT])),[Admissions_Current_Period])

 

3. Measure to get admissions for previous period:

Admissions_Previous_Period =
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE([Admissions_Current_Period],FILTER(ALL('Date'[Date]),DATEADD('Date'[Date],+1,YEAR) >= MinDate
&& DATEADD('Date'[Date],+1,YEAR) <= MaxDate)
)

4. Measure to get the cumulative admissions for previous period:

Admissions_Previous_Period_Cumulative =
VAR MinDate = MIN('Date'[Date])
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE( sumx(FILTER(ALLSELECTED('events'),[ADMIT_DT] <=MAX([ADMIT_DT])),[Admissions_Current_Period]),FILTER(ALL('Date'[Date]),DATEADD('Date'[Date],+1,YEAR) >= MinDate
&& DATEADD('Date'[Date],+1,YEAR) <= MaxDate)
)

 

Measure 4 is just adapting measure 2, to get cumulative, but i am obvious doing smething wrong?

Please can someone advise why my measure wont work?

 

thanks 

 

Riedwaan  

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.