Dax based on the result you obtained, you can use the following dax

9/4/2023

1. 
   To generate a calculated column that calculates the count of days until the closing date, using the expected closing date when the closing date is blank, you can use the following DAX expression:
   
   
   
   
   

dax
DaysTillClosing =
IF(
ISBLANK('YourTable'[ClosingDate]),
DATEDIFF('YourTable'[ExpectedClosingDate], TODAY(), DAY),
DATEDIFF('YourTable'[ClosingDate], TODAY(), DAY)
)
This expression uses the IF function to check if the closing date is blank. If it is blank, it calculates the number of days between the expected closing date and today using the DATEDIFF function. If the closing date is not blank, it calculates the number of days between the closing date and today. Replace "'YourTable'" with the actual name of your table, "'ClosingDate'" with the name of your closing date column, and "'ExpectedClosingDate'" with the name of your expected closing date column. The new calculated column "DaysTillClosing" will contain the count of days until the closing date, using the expected closing date when the closing date is blank.

2. 
   To generate intervals as a calculated column in DAX based on the result you obtained, you can use the following DAX expression:
   

dax
IntervalGroup =
SWITCH(
TRUE(),
'YourTable'[DaysTillClosing] <= 30, "1-30",
'YourTable'[DaysTillClosing] <= 90, "30-90",
'YourTable'[DaysTillClosing] <= 180, "90-180",
'YourTable'[DaysTillClosing] <= 365, "180-365",
"365+"
)

Replace "'YourTable'" with the actual name of your table, and "'DaysTillClosing'" with the name of your calculated column that contains the count of days until the closing date. This expression uses the SWITCH function in DAX, which evaluates a series of conditions and returns a result based on the first condition that evaluates to TRUE. In this case, it checks the value of the "'DaysTillClosing'" column and assigns the corresponding interval group based on the conditions provided. The new calculated column "'IntervalGroup'" will contain the interval groups for each row based on the count of days until the closing date. Note: Adjust the intervals in the SWITCH function as per your requirements
DAX
Interval Group =
SWITCH(
TRUE(),
RANKX(ALL('YourTable'[DaysTillClosing]), 'YourTable'[DaysTillClosing]) =1,"1-30",
RANKX(ALL('YourTable'[DaysTillClosing]), 'YourTable'[DaysTillClosing]) = 2, "31-90",
RANKX(ALL('YourTable'[DaysTillClosing]), 'YourTable'[DaysTillClosing]) = 3, "91-180",
RANKX(ALL('YourTable'[DaysTillClosing]), 'YourTable'[DaysTillClosing]) = 4, "181-365",
"365+"
)

In the above code, we use the RANKX function to assign a rank to each interval group based on the DaysTillClosing value. Then we use a SWITCH statement to assign the corresponding interval label based on the rank. This will ensure that the intervals are displayed in ascending order.

RESPONSE ”in my power bi report I have this measure to find previous month's value for kpi visual ,"Ой бошидаги колдик KPI = VAR CURRENT_BEGIN = MINX( FILTER('new_DIM_SBD_DEP_ACC_M', 'new_DIM_SBD_DEP_ACC_M'[OPER_DAY] IN ALLSELECTED('new_DIM_SBD_DEP_ACC_M'[OPER_DAY])), 'new_DIM_SBD_DEP_ACC_M'[OPER_DAY]) VAR prev = EOMONTH(EDATE((MINX( FILTER('new_DIM_SBD_DEP_ACC_M', 'new_DIM_SBD_DEP_ACC_M'[OPER_DAY] IN ALLSELECTED('new_DIM_SBD_DEP_ACC_M'[OPER_DAY])), 'new_DIM_SBD_DEP_ACC_M'[OPER_DAY])),-1),0) RETURN calculate (sum(new_DIM_SBD_DEP_ACC_M[SUM_DEP]) ,new_DIM_SBD_DEP_ACC_M[OPER_DAY]=prev)" , however it is not working properly because, -1 is getting an exact previous month's value , but the problem is that sometimes it is not getting the working day (oper_day), for example if the max value of selected month is 30-06-2023 this measure finds the value for 30-05-2023 but it is not a working day and as a result it is getting infinity or a blank in KPI's target, so please help me to fix this issue using dax!”
ANSWER
To fix the issue with your measure not always returning a working day for the previous month, you can modify your DAX measure by adding an additional step to find the last working day of the previous month. You can achieve this using the following steps:
1. Define a new measure called "Previous Month Last Working Day" that finds the last working day of the previous month:
* dax *
Previous Month Last Working Day =
CALCULATE (
MAX ( 'new_DIM_SBD_DEP_ACC_M'[OPER_DAY] ),
'new_DIM_SBD_DEP_ACC_M'[OPER_DAY] <= EOMONTH ( TODAY (), -2 ),
DAY ( 'new_DIM_SBD_DEP_ACC_M'[OPER_DAY] ) <> 0,
WEEKDAY ( 'new_DIM_SBD_DEP_ACC_M'[OPER_DAY] ) <> 1
)
This measure uses the "MAX" function to find the maximum OPER_DAY value that is less than or equal to the last day of the previous month ("EOMONTH(TODAY(), -2)"). Additionally, it excludes any days with a day number of zero or a weekday number of 1 (Sunday) to find the last working day.
2. Modify your existing measure "Ой бошидаги колдик KPI" to use the "Previous Month Last Working Day" measure:
dax
Ой бошидаги колдик KPI =
CALCULATE (
SUM ( new_DIM_SBD_DEP_ACC_M[SUM_DEP] ),
'new_DIM_SBD_DEP_ACC_M'[OPER_DAY] = [Previous Month Last Working Day]
)
This modified measure uses the calculated "Previous Month Last Working Day" measure as the filter condition to retrieve the sum of SUM_DEP for the last working day of the previous month. By incorporating these modifications, your measure should now consistently return the value for the last working day of the previous month, addressing the issue you mentioned.