I wanted to ask you guys if I can create a drop down filter/slicer that gives me figures and visualizations for Current Year, Current Month and Current Week. I understand that this is possible via drilldown in a Calendar table visualization. but that is not what I am looking for. I am trying to develop a drop down that allows me to filter by periods, and also allows my visualizations to change accordingly. So for instance, my ‘Sales Current Year Vs Last Year’ visualization changes automatically to ‘Sales Current Month vs Last Month’, as soon as I select Current Month from drop-down, instead of Current Year. If there is an existing tutorial on EDNA forum which addresses this issue, please guide me towards that. My understanding is that I have to create a new table that has YTD, Current Month and Current Week, but I am not clear.
I did not include the Week to Date logic to save on time, but the attached shows:
a table with a variable title (see “Variable Title Text” measure)
the title changes based on the value of the “Selected Period” measure
the “Selected Period” measure also drives the “Current Period” and “Prior Period” measures in the table. (unfortunately, I cannot make the measure names variable, so we can’t show MTD or YTD in the column headers)
a disconnected table called “Report Period” is used for the slicer option.
Now, this could instead be handled with two different tables and bookmarks (that you use buttons to switch between) - but that can get confusing fast if you have too many periods you are trying to report with.
@musa.fasih Using Tabular Editor to create calculation groups for the time intelligence or period slicers is very efficient. You can refer to this video to get started. Not only for time intelligence calculations, it is also used to switch between measures with a drop down slicer for example, Sales, Cost Profit e.t.c.
Let me know if you need more help or clarification on this subject. I will be happy to help.
sorry to bother you again, but when I try simple Sales, as opposed to YTD or MTD (which is cumulative in nature), it doesn’t work. My ‘current month sales’ measure, which ought to be triggered when I select Current Month from the slicer, is defined as follows:
My intention is to filter for the current month dynamically, without having to select the current month from the calendar visualization. However, there is something wrong with the way I am defining my filter in this measure.
Could you point out where is it that I am going wrong?
But, if that is not possible (although again, I highly recommend the switch), then you can try something like this:
Current Mth (Filtered Calendar) =
VAR _StartMonth = DATE( YEAR(TODAY()), MONTH(TODAY()), 1)
VAR _Current = TODAY()
RETURN
CALCULATE( [Total Sales],
FILTER( Dates,
Dates[Date] >= _StartMonth && Dates[Date] <= _Current ))
Note that these methods both specifically force to the CURRENT MONTH - your user won’t have the option to select a different month (for that you would need to identify the selected month and use that in the variables above).
