Forum Discussion

fab196's avatar
fab196
Icon for Helper II rankHelper II
4 years ago
Solved

COUNT VALUES FROM START OF MONTH USING DAX

HI 

 

i want to calculate total count 

here is my sql code 

 

declare @strdate date ='2021-09-21'

select CONVERT(date, cast( datefromparts(year(@strdate),month(@strdate),1) as datetime) , 120)

result of this query 

is 2021-9-01

 

so it is basically calculating total count from start of the month to till date as 

 

so iam using between operator in dax date column 

so how do i calculate the same in power bi 

suppose from date is 2021-09-21 and end date is 2021-09-21

so i want to calculate the data from 2021-09-01 to 21-09-2021 how to i do using dax

 

parry2k  needed your support as always you are .

@amitcha

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    4 years ago

    fab196 , if date is selected then you can use date tbale and time intelligence

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

     

     

    or

     

    with today

    MTD=
    var _min = eomonth(today(),-1)+1
    var _max = today()
    return
    CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

     

     

    with date

     

    MTD=

    var _max =date(2021,09,01)
    var _min = eomonth(_max ),-1)+1
    return
    CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    fab196 , if date is selected then you can use date tbale and time intelligence

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

     

     

    or

     

    with today

    MTD=
    var _min = eomonth(today(),-1)+1
    var _max = today()
    return
    CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

     

     

    with date

     

    MTD=

    var _max =date(2021,09,01)
    var _min = eomonth(_max ),-1)+1
    return
    CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Date] >= _min && 'Table'[Date] <=_max ) )

  • Nepal101's avatar
    Nepal101
    Icon for Helper III rankHelper III
    4 years ago

    In order to calculate the days between two dates, you can use Datediff.

    hope this helps.