Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX

Hi, I want to calculate a count of values in a column from the date of whatever the start date in my table till my filter selection (year & month )-1 For example, If I have data from Jan 2021 and M...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    >> in the slicer im using only Year and Month value.

    You can try this, create the measure below

     

    count = 
var _EndDate=DATE(SELECTEDVALUE('calendar'[year]),SELECTEDVALUE('calendar'[month]),1)
return
 CALCULATE( COUNTROWS(FactTable),FILTER(ALL(FactTable),FactTable[Date]<_EndDate))

     

     calendar table

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Refer suggestion by Signore_Ands . If that does not help, then refer

 

Using date table, you need to measure like

 

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

 

 

If you need a trend of 5 months then use an independent date table

 

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = eomonth(maxx(allselected(Date1),Date1[Date]),-1)
var _min = eomonth(_max,-1*MONTH(_max))+1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI