Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Referencing Signal Row Against Entire Column

I am using DateDiff to compare two different date columns. 

Deadline vs Finsh +/- =
    DATEDIFF (
        Tasks[TaskDeadline],
        (Tasks[TaskFinishDate].[Date]),
DAY
)
I want to be able to reference a signal row date only (Milestone - Notice to Proceed) against and entire column Tasks[TaskActualFinish] and compare the one row date, agaisnt the entire column. 
DAX

4 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi Anonymous to "agaisnt the entire column" you need some aggregation, like SUM, MAX, MIN...

    hope this help

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi some_bih, 

    I can do this in Excel but not sure how to transfer it to BI. To clarfiy what I mean I have included a table below. 

     

    To determine Days +/-, I have subtracting the Actual date (Column F)  from the Baseline Date (Column D). This will give me how many days I am passed our contracted date. 

     

     

    To determine how many weeks is passed I am subtracting all of Column F from D1, which is highlight in yellow (11/7/22). So I would need to do the same formula but instead of referencing all of Column D, I just want to reference D1, against all of Column F. 

     

    Hopefully this explains my issue a little more. 

     

    • some_bih's avatar
      some_bih
      Icon for Community Champion rankCommunity Champion
      3 years ago

      Hi Anonymous in Power BI there is something called calculated columns, similar to normal column in Excel so as I underdstan you can:

      a) create calculated column for difference  (name as you wish) and use formula 

      INT (Actual date (Column F)  - (minus) Baseline Date (Column D)), this will be calculate row by row

      b) you wrote "I just want to reference D1, against all of Column F" similar manner as you want single "cell" D1 to deduct by Column F entire, again you need calculated column (name as you wish) with formula which should use some aggregation, like SUM, MIN, MAX... Now I see your D and F column is date, this add complexity as "you can not sum dates" 😞 so another solution should be found.

       

      In general, in Power BI, when you work with dates / weekday, months... usually there is Calendar / Date table so you connect it with Data table (amounts etc.) and do calculation. Using slicer / filter for Dates level you wish results are automatically recalculated.

      So you need to go to your model / data and think what you need to calculate, keeping in mind that dates featues Power BI will handle.

      Hope this help / kudos appreciated.

  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    2 years ago

    I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

    Your insights and updates will greatly assist others who might be encountering the same challenge.