topic Re: Daily Changing Measure in DAX Commands and Tips

Daily Changing Measure

goncalves_iggor — Tue, 02 Jun 2020 15:35:51 GMT

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!

Re: Daily Changing Measure

AlB — Tue, 02 Jun 2020 17:07:56 GMT

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

Re: Daily Changing Measure

Anonymous — Wed, 03 Jun 2020 17:28:05 GMT

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

Re: Daily Changing Measure

stevedep — Thu, 04 Jun 2020 08:56:15 GMT

Does it have a timestamp for opened and closed?

Re: Daily Changing Measure

goncalves_iggor — Thu, 04 Jun 2020 11:41:32 GMT

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"

}

)

Re: Daily Changing Measure

goncalves_iggor — Thu, 04 Jun 2020 11:46:15 GMT

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.

Re: Daily Changing Measure

stevedep — Thu, 04 Jun 2020 12:04:47 GMT

https://community.powerbi.com/t5/Power-Query/Append-new-data-to-a-history-table-without-overwriting/m-p/758079/highlight/true#M25298

Re: Daily Changing Measure

Anonymous — Thu, 04 Jun 2020 13:13:54 GMT

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