Display results of last month from a folder with monthly reports

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I have created a report by loading monthly excel-files from a folder into my model and display the trend in a bar chart. Additionally I would like to create a pie-chart only of the most recent excel-file.
What would be your recommended approach?
Currently I have two different queries, one for all files and the other with a filter for the most recent file.
However I can imagine a more efficient approach. If so, please let me know.

Thanks in advance and looking forward to hearing from you.

Best regards
Frank

2

That certainly sounds like an ok approach.

Maybe a better one would be to create the different charts based on dates though.

So instead of have different tables in you model, try to create filters inside of DAX measues and use those in your visualizations. This would be my most preferred by far as it would give you a lot more flexibility with your follow up analysis.

It also would integrate much better with a fully functioning measure.

Let me know what you think.

3

Hi Sam,

thanks for the quick feedback. Yes, I also thought the DAX-measure approach would be the better way.
However I did not manage to define the correct DAX formulas.
My query reads Excel-Files in a folder with data from several months, which make up the trend.
Now I just want to display the result of the most recent data in a separate Pie-chart. Hopefully you can help me with that.
Again thanks and looking forward to hearing from you.
Best regards
Frank

4

I think if you need the exact most recent data, then you original idea is not a bad one.

The other idea you can have a play around with it place a time stamp or something identifier within an additional column AND in the most recent data.

Then post this still append the two table. At this point though you will have that identifier hard coded somehow within a column the you can use as a filter within you DAX measures.

You can create additional columns quite easily in the query editor. It could be a simple date or even just be a simple identifier, like “Historic”, “Latest”.

When these are in the one table then you have that column you can pass through into you measure to just show the either type of data.

This would honestly be the preferred option from me.

I still think getting into the one table is the best way forward.