Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chrispybacon
Helper I
Helper I

Default Date Values

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

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.).

13.JPG

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.