Forum Discussion

juankypanky's avatar
juankypanky
Frequent Visitor
7 years ago

Relative date filtering

Hi to all the group, this is Juan Carlos from Spain working with PowerBI since early 2018. I need a design of a dashboard with monthly KPIs. Every month I receive new data to display on it (from an ...
tjd's avatar
tjd
Impactful Individual
7 years ago

Have you tried using your Date field from your calendar table as a slicer and setting it to Relative and then selecting Last 1 Month?  As each day goes by, it updates the range to today's date:

 

 

 

juankypanky's avatar
juankypanky
Frequent Visitor
7 years ago

Yes, I tried... but this is not working for me... 

 

My calendar table is dynamically created like:

DataTable = 

VAR BaseTable =
    CALENDAR (DATE (2013;1;1);TODAY())
VAR AddYears =
    ADDCOLUMNS(BaseTable;"Año";YEAR([Date]))
...

If I try to select last 1 month from today (current-1) in the calendar table, I will obtain August (8), but my last file with information is July (7). This is the problem, that I need to obtain the relative data (month-1), (month-2) from the filed of my "Main Table", not with the field of the calendar table, because every day changes with the new days, months, etc. (sorry for my english, I try to explain the best for my level...). There is a gap between the current month and the last month introduced with data.

 

During this month (Septembrer) customers has the dashboard with July data, but available from 30 or 31 August. If the calculation is related to the "Calendar table" "current month -1" is not the same in August 30th, than in September 15th.

 

I thought to use the data field of my "Main table" to make this, filter the last month of the contained data (July). With this idea, the result month is the same each day until I load the new month information.

 

Hope I explianed my case clearly. Sorry for the mistakes.

Bye

JC