cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JavierGold
Regular Visitor

Displaying a measure total that ignores date slicer

I'm trying to use a measure to calc a total budget for the year to use in a gauge.   my formula is:

 

BudgetTotal = CALCULATE(sum('2017FinancialData'[Total]),'2017FinancialData'[type] = "Budget",All('2017FinancialData'[Total]))

 

I have a date slicer on the report that allows a user to select a date range...however, it is converting the measure to a YTD value instead of a Total for the Year.   How do I have this measure ignore the date slicer that I have on the report?

 

Thanks!

Jeff

1 ACCEPTED SOLUTION

Accepted Solutions
scottsen Memorable Member
Memorable Member

Re: Displaying a measure total that ignores date slicer

It's just general "best practices" to have a separate calendar table, and power bi helps you via CALENDARAUTO( ) function if you don't have one easily.  The idea is that you want a calendar w/ no "gaps" (missing days) ... and also fewer rows than would typically be in your large fact table.

 

Ignoring that if you want...  I would expect something more like:

 

BudgetTotal = CALCULATE(sum('2017FinancialData'[Total]),'2017FinancialData'[type] = "Budget",All('2017FinancialData'[DateField]))

 

But you need to keep it in the same year, so you would need a year column in your table and add it to your All as AllExcept or add another param to calculate that is VALUES(MyTable[Year]).

View solution in original post

4 REPLIES 4
scottsen Memorable Member
Memorable Member

Re: Displaying a measure total that ignores date slicer

Typically, you would have a separate data/calendar table, and that is where the slicer would come from... AND... where the dax measure should filter.

 

Your final ALL( ) looks a bit weird to me, I would expect something clearing some date fields instead.

JavierGold
Regular Visitor

Re: Displaying a measure total that ignores date slicer

Yeah...the All() was just an attempt to get the total.  

 

The data table has both budget and actual rows that includes a column for the date that each row is for.   I'm don't see how I could separate the date from that table?

scottsen Memorable Member
Memorable Member

Re: Displaying a measure total that ignores date slicer

It's just general "best practices" to have a separate calendar table, and power bi helps you via CALENDARAUTO( ) function if you don't have one easily.  The idea is that you want a calendar w/ no "gaps" (missing days) ... and also fewer rows than would typically be in your large fact table.

 

Ignoring that if you want...  I would expect something more like:

 

BudgetTotal = CALCULATE(sum('2017FinancialData'[Total]),'2017FinancialData'[type] = "Budget",All('2017FinancialData'[DateField]))

 

But you need to keep it in the same year, so you would need a year column in your table and add it to your All as AllExcept or add another param to calculate that is VALUES(MyTable[Year]).

View solution in original post

JavierGold
Regular Visitor

Re: Displaying a measure total that ignores date slicer

Thank you!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors