Forum Discussion

mweber87's avatar
mweber87
Helper I
3 years ago

Different start and end dates

I have a data set where there are multiple job_id's (5079) each job, which is labeled as job_id starts and stops on different dates. I want to create a column that shows the start week as 1 and the last week as the last number for each job. The duration of the job is tracked by transaction_amount. I tried this: (but it does a running total and does not seperate it by job_id)

Week Running Total by Job_Id =
VAR CurrentDate = d[transaction_date]
VAR CurrentJobTable =
    CALCULATETABLE ( d, ALLEXCEPT ( d, d[Job_Id] ) )
VAR Filtered =
    FILTER ( d, d[Transaction_date] <= CurrentDate )
RETURN
    COUNTX(Filtered, d[transaction_amount] )

 

Here is a snip of data to reference:

 

Need Help

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    mweber87 

    is this what you want?

    yearweek = year('Table'[start date])*100+WEEKNUM('Table'[start date])

Column = CALCULATE(DISTINCTCOUNT('Table'[yearweek]),FILTER('Table','Table'[job id]=EARLIER('Table'[job id])&&'Table'[yearweek]<=EARLIER('Table'[yearweek])))

    pls see the attachment below

  • seenavinu's avatar
    seenavinu
    Regular Visitor
    3 years ago

    I have a table with multiple flight numbers with a start date. But now I want to add different start and end dates for each of these flights (screenshot below). How do I add these columns so that only those dates that is specified for each flight will be reflected in my power bi dashboard? 

    Appreciate experts support on this.

     

     

    • ryan_mayu's avatar
      ryan_mayu
      Super User
      3 years ago

      seenavinu 

      it's better to open a new post, then more people can read and help you.

      What's the expected output for your sample data?

      What's the calculation logic?