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 My filter selection will be June 2022, my calculation should show from Jan 2021 to May 2022.
Please help me with the correct query.
TIA.

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.

5 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you, It worked!!

  • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

    Yes, there is. 

    Transactional data have dates but in the slicer im using only Year and Month value.

    I'm using this dax currently,

    QueuedLeads = CALCULATE(COUNT(table1[objectid]),USERELATIONSHIP(Calender[Date],table1[enteredon]))
     
    Its showing the count from Jan 2021 to till date but if i select the month from filter, it shows only for the current month.