Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filtering YearToDate Data

I have the formula

 

YTD_Cost = CALCULATE(COUNT('table'[cost]),DATESYTD('Date'[Date])

 

The formula correctly computes the count of all events for the current year.

I also have a formula that calculates LY YTD count of events which when i filter for a specific date range of this year. ie 01/05/2019 to 07/05/2019 it is showing the filtered amount of events for that date range last year.

Unfortunately, this year events is not filtering for the date range, but is showing a total count of all events that happened year to date.

Is there a formula to apply to count the values for the current year, so that when a filter is applied it shows that volume.

I want it to be setup so that each year it rolls over to show the current year data, without needing to make any changes to the formulas (ie. year=2019 to year = 2020)..

 

1 ACCEPTED SOLUTION

@Anonymous try following:

 

YTD_REDIRECTS = CALCULATE(COUNT('Table'[Event Type]),KEEPFILTERS(DATESYTD('Date'[Date]))


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

@Anonymous DATESYTD willl always count from Jan1 even you have filter applied and as the name suggest, it is Year to Date. I guess you are looking for if date filter is applied then use tht filter otherwise calculated ytd, is this correct understanding?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

That is correct @parry2k 

I have tried both of these formulas

YTD_COSTS = TOTALYTD(COUNT('Table'[Cost]),'Date'[Date])

YTD_COSTS = CALCULATE(COUNT('Table'[Cost]),DATESYTD('Date'[Date])

 

I have a date slicer, and it allows me to slice backward from today and get a valid value, however if i slice from january 1st (ie. from february to may) the number it shows does not change to that date range, it stays as from start of year.

 

For the time being, i have got as a measure

YTD_Costs = CALCULATE(COUNT(Table[Cost]),FILTER('Date','Date'[Year]=2019))
 
however this is not my ideal formula, as i want the report to require no adjustments in the future,
whereas with this formula it requires to be changed at the start of each year

@Anonymous try following:

 

YTD_REDIRECTS = CALCULATE(COUNT('Table'[Event Type]),KEEPFILTERS(DATESYTD('Date'[Date]))


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.