cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

Sum between two dates, ignoring the dates before my start of the year

Hello,

I have to calculate a SUM of my new clients between two dates.

- The date I choose from my filter

- The first day of the year of the date I choose from my filter

So for example, the date I choose from my filter is 23/05/2020. So my SUM has to be between 1/01/2020 and 23/05/2020. This SUM doesn't have to include all the dates before 1/01/2020.

So I did something like that:

Measure = CALCULATE(SUM('Table'[MY_NEW_CLIENTS);FILTER(ALLEXCEPT('TABLE';'TABLE'[CLIENT_TYPE_ID]); 'TABLE'[DATE] >= STARTOFYEAR('TABE'[DATE])&&'TABLE'[DATE] <=SELECTEDVALUE('TABLE'[DATE])))

The issue is, this measure also gives me the sum of all dates before my startoftheyear, whilst it should return only the sum between 1/01/2020 and 23/05/2020

Any ideas?

Thank you

2 ACCEPTED SOLUTIONS
Super User

Perhaps:

``````Measure =
VAR __Date = SELECTEDVALUE('Table'[DATE])
VAR __First = DATE(YEAR(__Date),1,1)
RETURN
SUMX(FILTER('Table',[DATE] >= __First && [DATE] <= __Date),[MY_NEW_CLIENTS])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Community Support

Hi @MCacc ,

You may create measure like DAX below.

``````Measure =

Var _FilterDate= SELECTEDVALUE( 'TABE'[DATE])

Var _FirstDate= DATE(YEAR ( 'TABE'[DATE]) , 1,1)

Return

CALCULATE(SUM('Table'[MY_NEW_CLIENTS),FILTER(ALLEXCEPT('TABLE', 'TABLE'[CLIENT_TYPE_ID]), 'TABLE'[DATE] >= _FirstDate&&'TABLE'[DATE] <=_FilterDate))``````

Best Regards,

Amy

Community Support Team _ Amy

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

4 REPLIES 4
Community Support

Hi @MCacc ,

You may create measure like DAX below.

``````Measure =

Var _FilterDate= SELECTEDVALUE( 'TABE'[DATE])

Var _FirstDate= DATE(YEAR ( 'TABE'[DATE]) , 1,1)

Return

CALCULATE(SUM('Table'[MY_NEW_CLIENTS),FILTER(ALLEXCEPT('TABLE', 'TABLE'[CLIENT_TYPE_ID]), 'TABLE'[DATE] >= _FirstDate&&'TABLE'[DATE] <=_FilterDate))``````

Best Regards,

Amy

Community Support Team _ Amy

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

Super User

@MCacc , is that not YTD ?

Example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...

Helper II

Thank you amitchandak! This worked perfectly for me. I had an excel file that is connceted to power bi and is formatted like a balance sheet so just numbers that need to be summed in order to get the number for the certain month.  I also tried one other solution in this post before trying yours and this was the lucky one!

Super User

Perhaps:

``````Measure =
VAR __Date = SELECTEDVALUE('Table'[DATE])
VAR __First = DATE(YEAR(__Date),1,1)
RETURN
SUMX(FILTER('Table',[DATE] >= __First && [DATE] <= __Date),[MY_NEW_CLIENTS])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.