Compare folder datasets and visualize the data by date

[Anonymous]

Hi there,

I'm a powerbi newby, and searched the forum and multiple other sites to find the answer. I think I have a lead what to start with (or use), but can't get it working.

I get my data via the folder option within Power BI. Every now and then a new version of a report is put into this folder. For analysis, I want to make a comparison between the latest report and the previous report and look at the cells (rows) which are new (compared to the previous version(s)) and which cells are gone.

As an example I check all new computer systems in our environment. Which systems are new and which systems are gone. So I want to visualize (in a matrix) by date, which items are new compared to last report(s) and which are gone compared to last report(s). I say report, because the delta is needed, but it would also be very nice, if I can see earlier delta's between the reports.

Here is an example of the data:

the first column is the name of the report, the second column, the system names. I can retrieve the date from the name of the report via the query editor, but now I want to show(visualize) the fields per date, which are new and which are gone. This can be off course two seperate visuals in the Report.

I think if have to use Rankx for this, but I'm really not sure where to start with. Hope someone can help me out.

[MFelix · Accepted solution]

HI @Anonymous ,

I was abble to do a approach to what you may want but this is just to help you think to give you a better way I would need further details on the expected outcome.

• Create a calendar table withou relation to your dataset 
• Now create the following measure:

ON / OFF =
VAR Selected_DAte =
    MAX ( 'Calendar'[Date] )
RETURN
    IF (
        CALCULATE (
            COUNT ( Table1[SystemNames] );
            FILTER ( ALL ( Table1[Date] ); Table1[Date] <= Selected_DAte )
        ) > 1;
        "Existing";
        IF (
            CALCULATE (
                COUNT ( Table1[SystemNames] );
                FILTER ( ALL ( Table1[Date] ); Table1[Date] <= Selected_DAte )
            ) = 1
                && CALCULATE (
                    COUNT ( Table1[SystemNames] );
                    FILTER ( ALL ( Table1[Date] ); Table1[Date] = Selected_DAte )
                ) = 1;
            "New";
            IF (
                CALCULATE (
                    COUNT ( Table1[SystemNames] );
                    FILTER ( ALL ( Table1[Date] ); Table1[Date] < Selected_DAte )
                )
                    = BLANK ();
                "Not Existing";
                "Off"
            )
        )
    )

• Use the calendar table as a slicer and then use the measure you created as a filter or a field in your visuals.

This measure can be changed to use number instead of texts to make calculations of quantities and not text values, believe that everything is working for picking up previous values and not existing in a certain date but may need adjustments.

Check attach PBIX file.

Regards,

MFelix

[Anonymous]

@MFelix I'm going to try this out. Thank you for your help!