Relative date period

[Anonymous]

Hi, 

I have an issue in my report. I would like to automatically display the last month of available data when opening the report, and let the user the choice in the period if he wants to display another month.

Today, it is ticked on one month and stay like this, it's not dynamic. It creates confusion for the users.

So today, it's a slicer for years + a filter for the month and it displays from January to the selected month of the year.

It's clearly not ideal. I tried to put only one slicer with directly the date inside but I want it to be displayed only by entire available months, not all the available dates and I couldn't find how to do this.

I've tried again with the "relative" option on my Datadate (DateDonnees field), putting a filter on my data date and then choosing "relative", last 1 month, but it gives me data that are not yet loaded/available in this field, I can't explain why 

Example (my last available data are 31/03/2020) :

If I let the slicer with the between option, the last data is ok, 31/03/2020:

Now, if I choose the "relative date" option, with Last 1 Month (calendar) choice, it gives me the the last month existing in reality, not in my data : 

Would you have a solution to have a proper filter, presenting automatically when opening the report the last month of available data and also allowing the user to choose another period (monthly period) if needed ?

The actual format of my dates are : 

DateDonnees : 31/03/20 00:00:00 : date type, always the last day of each month

I have the year in number : 2020

The month in letter : March

The month in text : 01 to 12

Could add the month in number if needed : 1 to 12

I can update easily my model if needed, let me know your thoughts, I would be very grateful for your help, it makes me crazy not to find a proper way to display such a simple feature...

Thanks a lot, AnneSo

[Icey · Accepted solution]

Hi @Anonymous ,

The Relative Date Period is based on the real date, not the date in your data.

So, it is suggested to create a calculated column in your Dates table based on the last date of your data.

For example, 

Relative Month Column = 
VAR MaxDate_ =
    MAX ( 'Fact Table'[Date] )
VAR MaxDateYear =
    YEAR ( MaxDate_ )
VAR MaxDateMonth =
    MONTH ( MaxDate_ )
VAR StartofMaxDate =
    DATE ( MaxDateYear, MaxDateMonth, 1 )
VAR StartofMonth_ =
    STARTOFMONTH ( 'Dates Table'[Date] )
RETURN
    SWITCH (
        TRUE (),
        StartofMaxDate = StartofMonth_, "ThisMonth",
        StartofMaxDate = DATEADD ( StartofMonth_, 1, MONTH ), "Last Month"
    )

BTW, .pbix file attached.

Best Regards,

Icey

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

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[Anonymous]

Hi Icey, 

Yes, I found another way to manage it because the user did not want to have the filters names "Last month", last 1 month , ...

Thanks for your help!

AnSo

[Icey]

Hi @Anonymous ,

Glad to hear that! Can you share us your solution?

Best Regards,

Icey

[Anonymous · Accepted solution]

Sure! I have split the date sélection, one part for year in which I have added one filter "current year", so that it stays on it and automatically refresh with the last available year. It can't be a slicer anymore but it's OK for the user.

And one part for the month with the automatic choice "last loaded month" and then other month displayed (here it's a period).

    

There is a small bug (tolerated because the gain is bigger than it), when you change the year selection, the last month choice remains in the filter choice, no data displayed but it should not be there. For example, swich from the current year to 2019, you can see the "Dernier mois chargé" (last loaded month) option still available in the filter whereas it doesn't exist for 2019. As soon as you click on another period, this line disappears and it's OK : 

Cheers, AnSo

[Icey · Accepted solution]

Hi @Anonymous ,

The Relative Date Period is based on the real date, not the date in your data.

So, it is suggested to create a calculated column in your Dates table based on the last date of your data.

For example, 

Relative Month Column = 
VAR MaxDate_ =
    MAX ( 'Fact Table'[Date] )
VAR MaxDateYear =
    YEAR ( MaxDate_ )
VAR MaxDateMonth =
    MONTH ( MaxDate_ )
VAR StartofMaxDate =
    DATE ( MaxDateYear, MaxDateMonth, 1 )
VAR StartofMonth_ =
    STARTOFMONTH ( 'Dates Table'[Date] )
RETURN
    SWITCH (
        TRUE (),
        StartofMaxDate = StartofMonth_, "ThisMonth",
        StartofMaxDate = DATEADD ( StartofMonth_, 1, MONTH ), "Last Month"
    )

BTW, .pbix file attached.

Best Regards,

Icey

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

[Anonymous]

Hi Icey, 

Thanks for your answer. Unfortunately, the pbix does not work : 

"Object reference not set to an instance of an object"

But I understand the concept so I will try it, I will let you know.

In the meantime, I am wondering how the user will be able to change to selected period afterwards if I put a filter on "This month" in my report ? 

Thank you, AnSo

[Icey]

Hi @Anonymous ,

Try to change the Relative month column like so:

Relative Month Column 2 =
VAR MaxDate_ =
    MAX ( 'Fact Table'[Date] )
VAR MaxDateYear =
    YEAR ( MaxDate_ )
VAR MaxDateMonth =
    MONTH ( MaxDate_ )
VAR StartofMaxDate =
    DATE ( MaxDateYear, MaxDateMonth, 1 )
VAR StartofMonth_ =
    STARTOFMONTH ( 'Dates Table'[Date] )
VAR DateDiffofMonth =
    DATEDIFF ( StartofMonth_, StartofMaxDate, MONTH )
RETURN
    IF ( DateDiffofMonth = 0, "This Month", "Last " & DateDiffofMonth & " Months" )

Best Regards,

Icey

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

[mwegener]

Hi @Anonymous ,

check this solution.

https://community.powerbi.com/t5/Desktop/Date-Filter-default-to-current-day/m-p/835608/highlight/true#M401341