Daily Changing Measure

[goncalves_iggor]

Hey everyone!

I'm struggling with this problem it's been a while and I didn't get to any results. I also din't find any similar problem with a sollution, so I decide to ask for help here.

I have a data table conected with a list in sharepoint, where my team launches the amount of jobs they are doing every day.

When they start a job they launch it as "Open" and as they finished it, they change that line to "Closed".

So I create a measure (Today's Open Jobs) that calculate the amount of Open Jobs we have left the last day, and every morning I update Power BI to show me that measure up to dated.

Also, I create a the following table to register the amount of Open Jobs each day of the mounth:

Where "Count" has the following formula:

Count = IF('DATABSE'[Date]  = TODAY(); [Today's Open Jobs]; BLANK())

The problem is that the value of the measure changes day by day, and the yesterday's value is lost. Also,

with the formula I've used, the collumn "Count" will only shows the value of today.

So I need a way to register the value of "Today's Open Jobs" in a way that the collumn "Count" will

fill itself day by day with the "Todays Open Jobs" of each day.

Hope someone can help me!

[stevedep]

Does it have a timestamp for opened and closed?

[Anonymous]

It's not enough to capture TODAY's data. You have to capture the history. So, in other words, every day you have to grab what there is and store it somewhere, possibly in a database, or in a file. Jobs should have ID's, of course. With these, you'll be able to track them through time. But the requirement is that every day you have to have a NEW snapshot of the jobs and their statuses saved somewhere. Then you'll combine all the historical data into a fact table from which you'll then be able to calculate what you need.

Best
D

[goncalves_iggor]

@Anonymous 

Exactly! Today I do it manualy in Excel. But that is exactly process de I want to "automatize" with power BI. As the responsible for the job changes it status from "open" to "Closed" instead of launch a new line, there is no history for status. I kinda need power bi to creat an "image" of the value, just like I do in Excel now.

[Anonymous]

As I said, every day you have to automate extracts and put them in the same folder. The names of the files should have a well-defined scheme as well. And then PBI can easily do the rest since Power Query can read a folder full of files with THE SAME STRUCTURE and load them all at once into a fact/dimension table(s).

So, use Power Query to your advantage.

Best
D

[stevedep]

https://community.powerbi.com/t5/Power-Query/Append-new-data-to-a-history-table-without-overwriting/...

[AlB]

Hi @goncalves_iggor

What is the code for your [Today's Open Jobs] measure and what does the data te measure uses look like? We need more info to come up with a solution

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[goncalves_iggor]

@AlB 

My table collumns are basicaly: 'Job Id'; 'Job Title'; 'Responsible'; 'Request Date'; 'Delivery Date'; 'Status'; 'Comments'.

To get the [Today's Open Jobs] I'm using:

Today's Open Items =

CALCULATE(

    COUNTA('Jobs 2020'[Responsible]);

        'Jobs 2020'[Status]

            IN {

                "Closed: Cancelled";

                "Closed: Finished"

                }

    )