cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Keano
Frequent Visitor

Allow a Slicer for Month the will support Current Month and Current Year to Date

 

 

HI

 

I have a Power BI desktop file, which on a simple level, has a Calendar and a Transaction Table

I'm happy with the content on both tables. In the Calendar table I have Current Year, Last Year, Current Month, Last Month etc and this table links to my transaction table on the date field

 

What I want to do, (and perhaps I'm overlooking something simple!) is allow the user to select a Month where one Amount column ( from transaction table) will show the Current Month Values and the 2nd Amount Column will show the YTD ( so everything less than or equal to the selected period)

The important thing is that the user can select, an example is that the user, in the present day, might want to see the report as it was in the past, so for example what the values were at the end of June, with values for that period and YTD to June

 

The Problem with using slicers is that it will filter the data to the current month only, but they are perfect otherwise as user can select easily on the report

I tried using Parameters and loading the Parameter to the report and applying it as a Slicer and imbedding it in the formulas for the amounts, but parameters only show the default as set on the parameter when you show it as a slicer on the report

So they do not allow the user to pick a value other that the default when using Power BI Online ( In Power BI desktop you have to use 'edit Queries' and 'edit parameters' which is not that user friendly

 

So I need to somehow imbed a function\parameter in my amount formulas that the user can easily change in the report

1 ACCEPTED SOLUTION
Keano
Frequent Visitor

Thanks

That did exactly what I wanted!

I thought it was something relatively straight forward, my misunderstanding was that slicers were like record selection filters ( like I was used to in other reporting tools) , so would filter all records for a visual or table no matter what, wasn't aware that some of the dax functions would work in conjunction with them like the YTD does, do any others do this?

Thanks again!

View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @Keano,

 

Not really sure if I understand your needs but I believe that what you need to implement is the TOTALYTD measure, in this case would be something like:

 

Total YTD = TOTALYTD(SUM(TransactionTable[Value]); CalendarTable[Date])

Then on your visual table you should place this measure and the Date from the calendar table.

 

No matter what month is selected on the slicer the YTD calculation will be given until that specific month/date

 

Regards,

MFelix

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Keano
Frequent Visitor

Thanks

That did exactly what I wanted!

I thought it was something relatively straight forward, my misunderstanding was that slicers were like record selection filters ( like I was used to in other reporting tools) , so would filter all records for a visual or table no matter what, wasn't aware that some of the dax functions would work in conjunction with them like the YTD does, do any others do this?

Thanks again!

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!