Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Days Between Table date and Selected dimdate or Max(dimdate[date])

Hi there,

 

I'm trying to calculate the age of issues from issue table in a report from a selected date or show the age of issues in bar graph by date?

For Instance, I have a column "date of issue" in issue table and I want to show in a bar graph that on this specific date that many issues were open and coloring stacked bar with the age, subtracting (date of issue from the date in Bar chart). 

if I just subtract it from today, then even for the historical date it's showing age (number of days from today's date). However, I'd want to show Age(number of days) from max dimdiate in the filter context.

 

Can someone please help? Thanks in advance

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi Anonymous ,

     

    Can you please share some sample data and expected result?

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for your reply! 

      I was actually able to figure it out and make it work. I created a table in and stored it in a variable, then i added a dynamic date column to it to get the difference and then produced a result. However, i'm worrying about Query performance now. Could you please help me make this code efficient. Code is below!

       

      Thanks in advance!

       

      SC_Backlog =
VAR DaysBucket_Min =
    MIN ( dim_Day_buckets[Min] )
VAR DaysBucket_Max =
    MAX ( dim_Day_buckets[Max] )
VAR date_to_examine =
    MAX ( dimDate[Date] )
VAR TabletoUse =
    CALCULATETABLE (
        CALCULATETABLE (
            FILTER (
                ADDCOLUMNS (
                    FILTER (
                        FILTER (
                            SafetyConcerns,
                            OR (
                                ISBLANK ( SafetyConcerns[Date of Closure] ),
                                SafetyConcerns[Has the concern been mitigated to the employee's or JHSC's satisfaction?] = "No"
                            )
                                || SafetyConcerns[Date of Closure] > date_to_examine
                        ),
                        SafetyConcerns[Date of Concern] <= date_to_examine
                    ),
                    "DaysFromExaminingDate", IF (
                        SafetyConcerns[Date of Closure] < date_to_examine
                            && SafetyConcerns[Date of Closure] <> BLANK (),
                        SafetyConcerns[Date of Closure] - SafetyConcerns[Date of Concern],
                        date_to_examine - SafetyConcerns[Date of Concern]
                    )
                ),
                [DaysFromExaminingDate] >= DaysBucket_Min
                    && [DaysFromExaminingDate] <= DaysBucket_Max
            ),
            CROSSFILTER ( dimDate[Date], SafetyConcerns[Date of Concern], NONE ),
            CROSSFILTER ( dimDate[Date], SafetyConcerns[Date of Closure], NONE )
        )
    )
VAR rowcount =
    COUNTROWS ( TableToUse )
RETURN
    IF ( ISBLANK ( rowcount ), 0, rowcount )