cancel
Showing results for
Did you mean:
Regular Visitor

## dax to calculate from first day of year to end of each Quarter

Hi,

in power bi desktop I have

1)following dataset which is calculating amount for each quarter means from January to March,April to June,July to September and October to December.

select datepart(QUARTER, entrydate_inrevenue) revenuequarter
,sum(amount)Amount
from Accounts
where
datepart(year, entrydate_inrevenue) = datepart(year,getdate())
group by datepart(QUARTER, entrydate_inrevenue)

But For my report I need to calculate cumulative values from january to March,January to June,January to September,January to December.which means from starting of year to end of each quarter.

I also have dimdate table in my database.

Can someone please explain how to implement this.

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: dax to calculate from first day of year to end of each Quarter

hi, @kan

After my research, you could try this way as below:

Add a Quarter NO column for date table

`Quarter NO = INT ( FORMAT ( [Date], "q"))`

Step2:

Add a measure by this formula

```result =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALL ( 'Date' ),
AND (
'Date'[Quarter NO] <= MAX ( 'Date'[Quarter NO] ),
YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )
)
)
)```

Result:

here is pbix, please try it.

https://www.dropbox.com/s/2lhx9l50z8q0xt7/dax%20to%20calculate%20from%20first%20day%20of%20year%20to...

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: dax to calculate from first day of year to end of each Quarter

hi, @kan

After my research, you could try this way as below:

Add a Quarter NO column for date table

`Quarter NO = INT ( FORMAT ( [Date], "q"))`

Step2:

Add a measure by this formula

```result =
CALCULATE (
SUM ( 'Table'[Amount] ),
FILTER (
ALL ( 'Date' ),
AND (
'Date'[Quarter NO] <= MAX ( 'Date'[Quarter NO] ),
YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )
)
)
)```

Result:

here is pbix, please try it.

https://www.dropbox.com/s/2lhx9l50z8q0xt7/dax%20to%20calculate%20from%20first%20day%20of%20year%20to...

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.