Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculation for the time/date

Good morning community,

 

Can anyone help me to make a calculation in PowerBI for this event in the red circle? any advice would be helpful.

 

 

Thank you

 

regards

 

Poko

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    Anonymous , By assuming from current details you have provided you must have an date column 

     

    Create a Calculated Column:
    Go to the "Modeling" tab in Power BI Desktop.
    Click on "New Column".

    OfWhichMoreThan5Years =
    IF(
        DATEDIFF([Date], TODAY(), YEAR) > 5,
        [TotalNumberOfLeadingCases],
        0
    )
     
    And if note please share more details for data and output
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago

    Ensure your dataset includes at least the following columns:
    State, Total Number of Leading Cases, Years in Stand

     

    New measure:

    CasesMoreThan6Years = 
    SUMX(
    YourTableName,
    IF(
    YourTableName[YearsInStand] > 6,
    YourTableName[TotalNumberOfLeadingCases],
    0
    )
    )

     If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

    You can create a measure as below to get the count of cases which more than 5 years:

    Case of which for more than 5 years = 
CALCULATE (
    DISTINCTCOUNT ( '4CecLeadingCase4'[CecCaseId] ),
    FILTER (
        'CecCaseDetail',
        DATEDIFF ( 'CecCaseDetail'[DefinitiveJudgmentDate], TODAY (), YEAR ) > 5
    ),
    'CecCaseDetail'[Case] = "Stand"
)

    Best Regards

7 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    Anonymous , By assuming from current details you have provided you must have an date column 

     

    Create a Calculated Column:
    Go to the "Modeling" tab in Power BI Desktop.
    Click on "New Column".

    OfWhichMoreThan5Years =
    IF(
        DATEDIFF([Date], TODAY(), YEAR) > 5,
        [TotalNumberOfLeadingCases],
        0
    )
     
    And if note please share more details for data and output
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      sure upload pbix??

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    Ensure your dataset includes at least the following columns:
    State, Total Number of Leading Cases, Years in Stand

     

    New measure:

    CasesMoreThan6Years = 
    SUMX(
    YourTableName,
    IF(
    YourTableName[YearsInStand] > 6,
    YourTableName[TotalNumberOfLeadingCases],
    0
    )
    )

     If this helped, a Kudos 👍 or Solution mark would be great! 🎉
    Cheers,
    Kedar
    Connect on LinkedIn

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      All columns must be in the same table?

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hello everyone thank you for responses it works someway but I Don't get the result marked in the red circle above.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Anonymous ,

      You can create a measure as below to get the count of cases which more than 5 years:

      Case of which for more than 5 years = 
CALCULATE (
    DISTINCTCOUNT ( '4CecLeadingCase4'[CecCaseId] ),
    FILTER (
        'CecCaseDetail',
        DATEDIFF ( 'CecCaseDetail'[DefinitiveJudgmentDate], TODAY (), YEAR ) > 5
    ),
    'CecCaseDetail'[Case] = "Stand"
)

      Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank you guys for help seems like it works I got difference due to missing dates in columsn but that's another story need to deal with backend guys,developers. Once again Thank you all.