DAX Measure to Calculate the Number of Open Issues Previous Weeks.

[Ninja_Powered]

My data source has a list of issues - each with an open date and a close date. I want to create a DAX measure that returns the number of issues open in a week, ie 2023 week 51 1000 issues were open (opened throughout the year not just week 51). I will display this on a chart with the axis being week number. 

To break it down I need a measure that returns all open issues before 'Week End Date'. I tried the DAX below but when I put it into a table it's only returning the number of issues opened in that week not the cumulative total of open issues before the end of that given week.

Any suggestions where I'm going wrong?

All Issues = 
VAR CurrentWeekEnd = MAX('Date Table'[End of Week])

RETURN
    CALCULATE(
        COUNTROWS('Issues'), 
        FILTER(
            'Issues',
            'Issues'[OPEN DATE] <= CurrentWeekEnd
        )
    )

[danextian]

hI @Ninja_Powered ,

Assuming that you have a separate Dates table, you can follow a formula similar to below:

All Issues = 
VAR CurrentWeekEnd = MAX('Dates2'[Date])

RETURN
    CALCULATE(
        [Sum of Values], 
        FILTER(
          ALL( 'Dates2'),
           'Dates2'[Date] <= MAX('Dates2'[Date])
        )
    )

You will see in the sceenshot below that it returns a running Sum of Values.

[Ninja_Powered]

@danextian That DAX isn't giving me the result I espected. I've created the table below to give an example. It is something for the total raised column I can trying to create.

Week Number	End of Week	Raised that Week	Total Raised
48	03/12/2023	10	10
49	10/12/2023	13	23
50	17/12/2023	5	28
51	24/12/2023	22	50
52	31/12/2023	12	62
1	07/01/2024	20	82
2	14/01/2024	18	100
3	21/01/2024	10	110
4	28/01/2024	6	116
5	04/02/2024	11	127

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  Ensure that there is a Calendar Table in that file with a Week number column.

[danextian · Accepted solution]

Hi @Ninja_Powered ,

The attached pbix has two approaches: one that uses a separate dates table and one that doesnt. While you can achieve a similar effect without using a separate dates table, adding columns to a visual that was not considered in the measure will give an unexpected result. It would be cumbersome to adjust the formula whenver a new column is added to a visual thus the use of an other dates table that contains the date related dimensions and use the columns from that table instead..

[Ninja_Powered]

@danextian The version with the calendar table worked for me - thank you! Just so I understand why are you using the REMOVEFILTERS function in the CALCULATE function?