Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 ) ) )
Best Regards!
Dale
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
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 ) ) )
Best Regards!
Dale
I was wondering, can you look at my forum issue and help :-). You seem to be the guru around here!
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
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |