Calculation for the time/date

[poko]

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

[bhanu_gautam · Accepted solution]

@poko , 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 · Accepted solution]

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

[poko]

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.

[poko]

Hello everyone thank you for responses it works someway but I Don't get the result marked in the red circle above. "the total leading stand" works as you see in the bpxis file. I attached pbix file please have a look where is the problem......

pbix file here:

https://easyupload.io/4zx0hl

[v-yiruan-msft · Accepted solution]

Hi @poko ,

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

[Kedar_Pande · Accepted solution]

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

[poko]

All columns must be in the same table?

[bhanu_gautam · Accepted solution]

@poko , 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

[poko]

sure upload pbix??