cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kan Regular Visitor
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
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"))

17.JPG

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:

18.JPG

 

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.
1 REPLY 1
Community Support Team
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"))

17.JPG

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:

18.JPG

 

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.