Calendar Date not working with created date and resolved date

[ashish18]

Hi Guys,

 I am trying to get total open defect Vs closed defect per day from Source file. Source file has created on date and Resolve date. I want these both dates to behave with calender date.

I have created date table using below code and also created measures for total open defects and closed defect.

I have set relationship of datetable date with created on date on file

another relationship is inactive with resolved date as its not possible.

Now,I am getting open defects correctly but closed defects not working with date.

DateTable =

ADDCOLUMNS (

    CALENDAR (MIN('SourceFile'[Created on Date]), MAX('SourceFile'[Resolved])),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "MMMM"),

    "MonthNumber", MONTH([Date]),

    "Weekday", FORMAT([Date], "dddd")

)

Total Closed Defects =

CALCULATE (

    COUNTROWS ('SourceFile'),

    USERELATIONSHIP (DateTable[Date], 'SourceFile'[Resolved])

)  

Total Open Defects =

CALCULATE (

    COUNTROWS ('SourceFile'),

    FILTER (

        'SourceFile',

        'SourceFile'[Created on Date] <= MAX(DateTable[Date]) &&

        (ISBLANK('SourceFile'[Resolved]) || 'SourceFile'[Resolved] > MAX(DateTable[Date]))

    )

)

Can you please help me to get closed defects to reflect by day. Thanks in advance.

[vojtechsima · Accepted solution]

@ashish18 ,

hey, I recreated the measures and everything you had, and I have no issues; it must be the DateTime in the Resolved Date, perhaps.

The 9 likely represents the 9 missing Resolved Dates in the source I had.

Check out my file and compare it with yours.

[vojtechsima]

@ashish18 hey, double check that all dates you use to build a calendar and the calendar itself it set to Date not Datetime.

[ashish18]

HI @vojtechsima  Thnaks for ur response.. Even if I change it to datetime,it still give same issue

[vojtechsima]

@ashish18 , Change it do DATE, not datetime. Otherwise, all seems good. If you have sample pbix file , you can share it through OneDrive/Google Drive.

[ashish18]

HI @vojtechsima   Sorry if I missed anything. DateTable already has Date

DateTable =

ADDCOLUMNS (

    CALENDAR (MIN('SourceFile'[Created on Date]), MAX('SourceFile'[Resolved])),

    "Year", YEAR([Date]),

    "Month", FORMAT([Date], "MMMM"),

    "MonthNumber", MONTH([Date]),

    "Weekday", FORMAT([Date], "dddd")

)

[vojtechsima · Accepted solution]

@ashish18 ,

hey, I recreated the measures and everything you had, and I have no issues; it must be the DateTime in the Resolved Date, perhaps.

The 9 likely represents the 9 missing Resolved Dates in the source I had.

Check out my file and compare it with yours.

[ashish18]

Hi @vojtechsima Really appreciate ur help. It was a minor mistake...Resolved date in sourcr file was dateTime type.I changed it to Date and it worked now. 🙂  Thanks Again!!

[vojtechsima · Accepted solution]

@ashish18 , thought so, good you found it. If you could please mark my answer as the solution to close the thread. Thanks