Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Open Ticket Count

Hi Everyone,   I have a below requirement and I am struggling to get this done and looking for some help. If anyone has any suggestions on how to acheive this, please let me know and that would be ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

I create a sample by your data. My calendar table is as below.

DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2022, 03, 31 ) ),
    "MonthYear",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "MonthYearName", FORMAT ( [Date], "MMM-YY" )
)

Please try my codes to create measures.

Basic Flag = 
VAR _Actual_start = MAX('Table'[Actual start])
VAR _Actual_end = MAX('Table'[Actual end])
VAR _Month_end = MAX(DimDate[Date])
RETURN
IF(_Actual_start<=_Month_end,IF(_Actual_end = BLANK() || _Actual_end>=_Month_end,1,BLANK()))
Sum = 
SUMX(VALUES('Table'[ID]),[Basic Flag])

Result is as below.

 

Best Regards,
Rico Zhou

 

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Rico,

     

    Thank you for the help, for some reason still i am not able to get the expected result set, here is the expected result set but i am getting 12 for the month of Feb 2021

    this is the expected resultFiltered on End date (tickets opened and closed on Jan and Feb)Filter on Actual startfor feb it is showing 12 it should be 23