Statu Measure Calculating

[erhan_79]

Hi there ;

I need your support for below issue ;

i have a table as below , i just try to compare reuest date and Quantity vs. confirmation date and qty . İf confirmation quantities compares request quantities on time , then i want to get   " close " statu with a mesaure . I can not create a column because i am working on azure server with a live data. so i need a measure i think.

So which rules will work ; 

• Request Date  : for request date calculating system will consider if request date is in this month or beforethis month  ( for today if we are checking report system will care 31.01.2021 and before dates) 
• Confirmation Date : For confirmation date calculation system will consider only the actual month when we open the report .If we are in January system will consider confirmations which are in january .

So for below sample imagine that we are in january 2021 now.And we are opening report today  , our measure will calculate statu of order  like that ; 

System will calculate based on order line , but will give only one  result based on order ( i will explain soon ) , so as you see below , in an order line , if the request date is in this actual month or earlier system will took that order qty , and will compare with confirmation qty and date , if in the same line request qty total is equal with confirmation qty and if the confirmation date is in the actual month that we are opening the report so this line statu will be for us closed.As you see for all lines based on one order number all of them has closed statu . So if an order's all lines calculated  closed statu i want from system to give with a measure that order1 statu is closed .

this will work only for the orders whcih one's  all lines has "Closed" statu and available for the rule that i mebtioned above .If the one of line will not have this rules that order statu will be "Opened" 

i hope it is clear thanks in advance for your kind supports 

[Anonymous · Accepted solution]

@erhan_79 

Try to create a flag measure first to see whether each line matches the condition, I am not sure about the condition you wrote that is a bit confuse to me, but the flag measure can be something like: 

Match condition? = IF(MAX([Request Date].[MonthNo])<=MAX([Confirmation Date].[MonthNo]) 
                        || MAX([Request Date])<=MAX([Confirmation Date]) 
                            && MAX([Order Quantity])=MAX([Confirmation Quantiy]),TRUE(),FALSE())

Then you can create the status measure to check whether all line are true: 

Status = 
var countall_   = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),[Order Number]=MAX([Order Number])))
var countclose  = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),[match condition]=TRUE()),FILTER(ALL('Table'),[Order Number]=MAX([Order Number])))
Return IF(countclose=countall_,"Close","Open")

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.