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

[kan]

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.

[v-lili6-msft · Accepted solution]

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