Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a running total measure that calculates till the last month on the calendar. How can I change it so it can calcuate till the current monthly only? I tried change Max date to <= today () but it did not work.
Forecast Cumulative = CALCULATE(
SUM(' Schedule'[Forecast]),
USERELATIONSHIP(CalendarM[Date],'Schedule'[Forecasted Date]),
FILTER(
ALLSELECTED('CalendarM'[Date]),
ISONORAFTER('CalendarM'[Date], MAX('CalendarM'[Date]), DESC)
)
)
Thanks in advance!
Solved! Go to Solution.
@ElenaJ , Try like
Forecast Cumulative = CALCULATE(
SUM(' Schedule'[Forecast]),
USERELATIONSHIP(CalendarM[Date],'Schedule'[Forecasted Date]),
FILTER(
ALLSELECTED('CalendarM'[Date]),
ISONORAFTER('CalendarM'[Date], MAX('CalendarM'[Date]), DESC)
&& 'CalendarM'[Date] <= today()
)
)
Or
if( max('CalendarM'[Date]) <= today() ,CALCULATE(
SUM(' Schedule'[Forecast]),
USERELATIONSHIP(CalendarM[Date],'Schedule'[Forecasted Date]),
FILTER(
ALLSELECTED('CalendarM'[Date]),
ISONORAFTER('CalendarM'[Date], MAX('CalendarM'[Date]), DESC)
&& 'CalendarM'[Date] < today()
)
) , blank() )
@ElenaJ , Try like
Forecast Cumulative = CALCULATE(
SUM(' Schedule'[Forecast]),
USERELATIONSHIP(CalendarM[Date],'Schedule'[Forecasted Date]),
FILTER(
ALLSELECTED('CalendarM'[Date]),
ISONORAFTER('CalendarM'[Date], MAX('CalendarM'[Date]), DESC)
&& 'CalendarM'[Date] <= today()
)
)
Or
if( max('CalendarM'[Date]) <= today() ,CALCULATE(
SUM(' Schedule'[Forecast]),
USERELATIONSHIP(CalendarM[Date],'Schedule'[Forecasted Date]),
FILTER(
ALLSELECTED('CalendarM'[Date]),
ISONORAFTER('CalendarM'[Date], MAX('CalendarM'[Date]), DESC)
&& 'CalendarM'[Date] < today()
)
) , blank() )
User | Count |
---|---|
50 | |
24 | |
18 | |
17 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
21 |