topic Re: [Unsolved] Showing historic snapshot reports (urgent) in DAX Commands and Tips

[Unsolved] Showing historic snapshot reports (urgent)

FU — Sun, 25 Dec 2022 01:56:14 GMT

Hi all,

I am looking for some support in visualisng my monthly snapshot reports that are connected to my SharePoint folder.

I have provided a set of snapshot Excel files and I am seeking someone's help to pull this together into Power BI and visualise it in this way (and if possible sending back the pbix file for reference)

I am looking to visualise this roughly (but open to suggestions) in the format below:

1) A date slicer so that I can filter between any 2 days and see the changes that have occured (Snapshots taken once a month)

2) Be able to filter by 'Portfolio Name' 'Programme Name' 'Project Name' using the filters on the left (approx 80% of data is linked to Projects)
2.1) 'Portfolio Name' can be found in the Portfolios table. 'Programme Name' can be found in the programmes table. 'Project Name' can be found in the overview table.

3) Filter by the table name using the filters on the bottom left of the page (these are also the tables shown in the Excel files linked)

My Excel files are captured in SharePoint folder in the format below:

These files can be found here: https://we.tl/t-MooX7SGKRV

I have changed the data in each file so that the changes can be visualised for demonstration purposes.

Would anyone be helpful enough and visualise this for me?
If anyone has any better ideas to show historic data using a date slicer please also drop your suggestions!

This work is quite urgent for me so would appreciate any fast answerers 🙂
If you need any further clarification please let me know here or DM.
Thanks!

Re: [Unsolved] Showing historic snapshot reports (urgent)

lbendlin — Sat, 24 Dec 2022 14:09:00 GMT

A date slicer so that I can filter between any 2 days

That means you need to get the file dates somehow, either as part of the file name (preferred) or using the Created Date meta data (ok-ish) or the Modified Date (questionable)

Filter by the table name

This means you need as many Power Query queries as you have sheets/tabs/tables you want to compare. Comparison would be between the snapshots of each sheet/tab/table, not across the entire Excel file. If you want data model level snapshot comparisons then you will have to define the measures that should drive that.

Re: [Unsolved] Showing historic snapshot reports (urgent)

FU — Sun, 25 Dec 2022 01:55:34 GMT

Still unsolved

Re: [Unsolved] Showing historic snapshot reports (urgent)

FU — Mon, 26 Dec 2022 09:54:34 GMT

I have queries for each table I've imported with "date created" columns.

How can I also get my (project, programme, portfolio) filters to link to my appendid query with all the sheets

Re: [Unsolved] Showing historic snapshot reports (urgent)

lbendlin — Mon, 26 Dec 2022 13:56:12 GMT

you need to run separate queries, one per sheet name.

Re: [Unsolved] Showing historic snapshot reports (urgent)

FU — Mon, 26 Dec 2022 14:00:08 GMT

I have done that and appendid the queries into 1. How do I link Portfolio programme and Project so that I can use them as filters?

Re: [Unsolved] Showing historic snapshot reports (urgent)

lbendlin — Mon, 26 Dec 2022 14:05:22 GMT

I have done that and appendid the queries into 1

I don't think you want to append them as the sheets have different formats. Each comparison will have to be tailored to the sheet structure.

Re: [Unsolved] Showing historic snapshot reports (urgent)

FU — Mon, 26 Dec 2022 14:14:12 GMT

Once I imported the sheets I added an index an un pivoted other columns. Then I appendid so they all had (source name, index, Value, table name) that way I could combine all in one