Forum Discussion

ThomasWeppler's avatar
ThomasWeppler
Icon for Impactful Individual rankImpactful Individual
4 years ago
Solved

How do I get a different result on a specific date?

Hi Power BI community,

I want to have a measure where I calculate the an increaseing value on diffrent assingments.

However on the date the assingment gets closed I want to reduce the calculation with the sum of all other days.

I tried this

IGA =
var _closedate = calculate(FIRSTDATE(Assignment[closedate]),USERELATIONSHIP(Assignment[Closedate], Kalender[Date]))
return
IF('Measure'[Income] < 'Measure'[expenses],0,
if((Kalender[Date]) =  - 'Measure'[E. Avance] * [FG],
'Measure'[E. Avance] * [FG]))

The problem is that I don't know how to pick a specific date since today(), lastdate() or maxdate() will get me the last date in my calender table.

The reason I want to build it like this is because I want a matrix where the value keeps increasing, but goes to zero the date the assingment is closed. But i am awre that I might need to rethink how it is build.

I hope I have done a good enough job explaining the challenge otherwise feel free to ask.
Need Help
  • selimovd's avatar
    selimovd
    4 years ago

    Hey ThomasWeppler ,

     

    it's a little tough to give advice withouth furthere details.

    First of all to make the code more readable, you should write measures without a table and columns with the table name. I guess everything from the table 'Measure' is a measure and Date is a column. Then you would need an aggregation for that column.

    Try the following approach:

    IGA =
VAR _closedate =
    CALCULATE (
        FIRSTDATE ( Assignment[closedate] ),
        USERELATIONSHIP ( Assignment[Closedate], Kalender[Date] )
    )
RETURN
    IF (
        [Income] < [expenses],
        0,
        IF (
            MAX ( Kalender[Date] ) = - [E. Avance] * [FG],
            'Measure'[E. Avance] * [FG]
        )
    )

     

    If I misunderstood, please give me a few more details or in the best case a demo file.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi
    Follow me: twitter.com/DenSelimovic

3 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hey ThomasWeppler ,

     

    it's a little tough to give advice withouth furthere details.

    First of all to make the code more readable, you should write measures without a table and columns with the table name. I guess everything from the table 'Measure' is a measure and Date is a column. Then you would need an aggregation for that column.

    Try the following approach:

    IGA =
VAR _closedate =
    CALCULATE (
        FIRSTDATE ( Assignment[closedate] ),
        USERELATIONSHIP ( Assignment[Closedate], Kalender[Date] )
    )
RETURN
    IF (
        [Income] < [expenses],
        0,
        IF (
            MAX ( Kalender[Date] ) = - [E. Avance] * [FG],
            'Measure'[E. Avance] * [FG]
        )
    )

     

    If I misunderstood, please give me a few more details or in the best case a demo file.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi
    Follow me: twitter.com/DenSelimovic

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi ThomasWeppler ,

    Seems like your formula is not complete, the variable "_closedate" does not appear in the return part, and for "IF ( Kalender[Date] = - 'Measure'[E. Avance] * [FG]" part, what is E.Avance and FG, how can it equal to a date.

    Need more explaination and examples.

    Best Regards,
    Community Support Team _ kalyj

  • ThomasWeppler's avatar
    ThomasWeppler
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    I have worked a bit more with it and the max(kalender[date]) helped me to get the result that I needed so thanks for the help.