Measure that displays month when value for column X changed

[Jorritster]

Hi all,

I am trying to create a seemingly easy measure in Power Bi.  I have a year, month and a binary variable called Threshold_Passed. I  want to create a measure that contains the month in which the Threshold_Passed measure changed from False to True, because only in that month I need to check it. If it is True again in April, I can assume it has already been checked last month.

It can also happen that in the first month of the year the Threshold_Passed measure is already True. In that case the Month_Threshold_Passed should be January. Once the Threshold_Passed variable has a True, it can never go back to false within the same year.

Year	Month	Threshold_Passed	Month_Threshold_Passed
2022	January	False
2022	February	False
2022	March	True	March
2022	April	True	March

Any help is appreciated. Let me know if you need any more information.

Kind regards, Jorrit

[v-yanjiang-msft · Accepted solution]

Hi @Jorritster ,

According to your description, I download your sample, here's my solution.

Create a measure,

Month_Threshold_Passed =
VAR _MIN =
    MINX (
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
                && 'Table'[Threshold_Passed] = "True"
        ),
        'Table'[Date]
    )
RETURN
    IF (
        MONTH ( MAX ( 'Table'[Date] ) ) < MONTH ( _MIN )
            || NOT ( ISINSCOPE ( 'Table'[Customer] ) ),
        BLANK (),
        FORMAT ( _MIN, "MMMM" )
    )

Get the result.

I attach my sample for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jorritster]

Hi @v-yanjiang-msft, one additional question. Ideally, for my dashboard I want the user to be able to filter with a slicer on the Month_Threshold_Passed. After Googling I found out that I need to create a column for this. However, if I use the same code it does not understand the relationships. This is my model:

How can I include related(table) in the code to make it work for a column? I have a bridge direction table because otherwise I have a many-to-many relationship. 

Hope you can help 🙂

[Jorritster]

Hi @v-yanjiang-msft,

Your a hero. Thanks for your help. 🙂

I have accepted your post as a solution. Cheers!

[lbendlin]

You can use MIN() or FIRSTNONBLANK() or many other approaches for this.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Jorritster]

Hi Ibendlin,

Thank you for your response. 

As the data is confidential I did not add it initially. However, I have created some dummy data based on manual entry that covers an example. I have not included the model as I am using a lot of different sources:

https://www.dropbox.com/s/sxt9vju0xkhbn5t/Dummy_Threshold_Management.pbix?dl=0

To give you some more perspective (in the actual model): 

- YTD_TaxableBasis is a YTD calculation of Monthly TaxableBasis.

- Threshold is coming from a different table (each country has a different threshold).

- Threshold_Passed equals True if YTD_TaxableBasis > Threshold.

Goal: create an indicator (Month_Threshold_Passed) to know when the threshold was passed. 

Expected output:

Hope this helps, Jorrit