Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Power BI community! :),
this is my first post - So I have the following question:
In my requirments I need the user to directly see the previous month in the dashboard. This means that I need to set a default value, which should always be the last month. I receive the dates from the DW in a granularity level of months, so I grouped my dates in my Time dimension into monthly bins. The user has a slicer on the dashboard, which allows him to switch between those bins.
The relative slicer is also not an option, as the user should simply open the report, and not have to change anything with the filters.
One way of doing it would be using the PREVIOUSMONTH function in combination with TODAY(), and filtering all the measures Via Calculate. But then I am not sure how they would behave if I want to slect a different month. Also I have a lot of KPIs.
Is there a way to solve this via Parameters? I wasn't able to fully wrap my head around the concept yet. Any ideas will be much appreciated.
Best regards,
Chris
Solved! Go to Solution.
hi, @chrispybacon
You may try this way as below:
Add a column for date column by this formula
Column = IF( DATEDIFF('Date'[Date],TODAY(),MONTH)=1,"previous month")
Then drag this field into a slicer or page level filter to filter the data.
Best Regards,
Lin
hi, @chrispybacon
You may try this way as below:
Add a column for date column by this formula
Column = IF( DATEDIFF('Date'[Date],TODAY(),MONTH)=1,"previous month")
Then drag this field into a slicer or page level filter to filter the data.
Best Regards,
Lin
Hi @v-lili6-msft,
thanks for the valuable input, yes this seems to work nicely.
One problem I am still facing though, is that if I want the user to have the ability to change the reporting period, this top level filter restricts the selection in the other slicers. If I put the Previous month filter on a visual level filter, the other slicers still don't seem to affect the selected visual.
Any ideas on how I could "bypass" the filter in case of a different selection?
hi, @chrispybacon
Column = IF( DATEDIFF('Date'[Date],TODAY(),MONTH)=1,"previous month","not previous month")
You could drag this field into a slicer do not drag it into visual\ page\ report level filter to filter the data.
If so when don't select any field or select all fields into slicer, they can change the reporting period optionally, and if only
select "previous month", It will just previous month data.
Best Regards,
Lin
hi @v-lili6-msft,
yes when putting the calculated column in the slicer that works. Previous month returns me all the values of November (Now its Dec.), which is exactly what I want.
I would also like the user to have the option to do the same thing for the previous year. I noticed that when I use the datediff function it returns all the values in the last year. At least when I filter in my data view by previous year it only shows me all 365 days of 2017. What I would like to have is a date range of 06.12.2017 - 30.11.2018. So the current month should not be included in this filter.
I used the following Dax for the previous year:
Previous Year = IF( DATEDIFF( 'Time Dimension'[Date]; SELECTEDVALUE( 'Time Dimension'[Date (bins)]; TODAY()); YEAR) = 1; "previous year")
Maybe it is important to note that due to the granularity in my data, the date are only delivered in months. So to be able to calculate with them I binned my time dimension by Months, and made a new date in all my tables by adding a "01" to my date columns.
Any ideas what would be a good way to tackle this?
hi, @chrispybacon
Your formula doesn't work for calculate column.
try the formula like this
Column = IF( DATEDIFF('Date'[Date],TODAY(),MONTH)=1,"previous month",IF( DATEDIFF('Date'[Date],TODAY(),MONTH)=13,"previous year previous month","not"))
then drag it to slicer, you could select previous month "2018/11", previous year previous month "2017/11" and others.
of course, you could continue to add conditional like:
DATEDIFF('Date'[Date],TODAY(),MONTH)=25
DATEDIFF('Date'[Date],TODAY(),MONTH)=37, ect.
also, you could use this formula to get all the previous month based on current month for each year.
Column 2 = IF(( DATEDIFF('Date'[Date],TODAY(),MONTH)-DATEDIFF('Date'[Date],TODAY(),YEAR)*12)=1,"previous month","not previous month")
for example:
Previous month returns me all the values of November for each year (Now its Dec.).
Best Regards,
Lin
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |