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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Umadhandapani
Helper II
Helper II

Alternate for All function at low-level

Hi,
I have used the following measures,
_Amount =
var _max= max('calendar1'[month])
var _min=calculate(MAX('Calendar'[Date]),'Calendar'[Month]=_max)
Return
calculate([Amount],filter(ALL(Calendar),Calendar[date]<=_min))

to calculate the amount till selected fiscal year and month.For example, if I select FY22, it should display the value form the start of year( may be data hold from FY20), it should sum the value from FY20 till FY22 selected month.
This work fine at matrix level. But, when I drillthrough, it should split the value at date-level, but it is not working. 
Either it shows without splitting or all values duplicated at each date level, because of All function.
Can suggest alternate for All function at low level?

1 REPLY 1
amitchandak
Super User
Super User

@Umadhandapani , Can you use datesytd if you have date table

 

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

 

or

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.