Forum Discussion

goncalves_iggor's avatar
goncalves_iggor
Regular Visitor
6 years ago

Daily Changing Measure

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!
 
 

7 Replies

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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's avatar
      goncalves_iggor
      Regular Visitor
      6 years ago

      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"
                      }
          )
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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