cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juankypanky Frequent Visitor
Frequent Visitor

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 Excel file). My model takes this Excel and others month to month from a shared folder. The excel files includes around 13000 rows each one with a data identifier filed with the last day of the month, so the structure is similar to this:

 

NAME | SURNAME | POSITION | ... | DATAFIELDID |

Name1 | Surname1 | Manager1 | ... | 31/07/2018 |

Name2 | Surname2 | Manager2 | ... | 31/07/2018 |

Name3 | Surname3 | Manager3 | ... | 31/07/2018 |

 

I have a "Data Table" (created with DAX dinamically) and I created a relationship between the "DATAFIELDID" and the "DATA" field of the data table.

 

I am using now the DATAFILED to filter for the last month than I have information of (manually), so every month I need to select the new data of this field. I wonder if I can filter for the MAX value of the content in DATAFIELDID to save me this montlhy tasks. 

 

For example:

The last Excel wich I received is from July and the data value of the DATAFIELDID is 31/07/208. At the end of this month I will receive another Excel with 31/08/2018.

 

I'm sure this is an easy question for most people using Power BI but as I am a newby, I have a lot of doubts related to what can I do with this tool.

 

Thank you very much in advance,

Bye!

JC

2 REPLIES 2
tjd Established Member
Established Member

Re: Relative date filtering

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:

date.png

 

 

 

juankypanky Frequent Visitor
Frequent Visitor

Re: Relative date filtering

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