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

[Keano]

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

[MFelix]

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

[Keano · Accepted solution]

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!