Create column based on two other columns

[Anonymous]

Hi everyone,

I have a questions, which might seems to be easy, but I was not able to handle it.

I have the following data:

CreationDate and ClosedDate is any date, ReportDate is always the last of a month or, if the last of a month is not reached yet, the current day (e.g. 10.02.2020). ID is an identifier and xyz a description.

My goal is now, to have a column which shows 0 or 1. 0 if the CloseDate is empty OR if the close date is within the same month (and year) of the ReportDate). If the month and the year of the report date is greater than the CloseDate or the CloseDate is not empty, it shall be 1. 

The overall goal, is to have a matrix, which shows for each ReportDate the count of the status within the ReportDate. In this example, it would be: 

(I only want to have the records displayed in the ReportDate which have the same month and year as the ClosedDate and not after the ClosedDate)

My idea with the column OutOfRange is to filter for OutOfRange = 0, in order to display only the values which are within the range.

Any help is appreciated.

Thank you in advance for your efforts

Best
J

[amitchandak]

Please check this blog how to deal with start and end dates. Very similar to your problem

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

@amitchandak, very informative! Thank you for the link!

[Mariusz · Accepted solution]

Hi @Anonymous 

Please see the attached file with a solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi @Mariusz,

thank you very much for your help and your effort! Exactly what I was looking for.

I have one additional question for understanding: It would be possible to use the column OutOfRange also (instead of the measure), but then I would just create the sum, regardless the ID? (I see that with the measure, I count the occurrence only once per month and status).

Thank you again,

Best

J

[Mariusz]

Hi @Anonymous 

I've adjusted it to use a Column, please see the attached.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn