Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Ninja_Powered
Frequent Visitor

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

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
        )
    )

 

1 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..

danextian_0-1708125944905.png

danextian_1-1708126299819.png

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

5 REPLIES 5
danextian
Super User
Super User

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.

danextian_0-1708075053196.png

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@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 NumberEnd of WeekRaised that WeekTotal Raised
4803/12/20231010
4910/12/20231323
5017/12/2023528
5124/12/20232250
5231/12/20231262
107/01/20242082
214/01/202418100
321/01/202410110
428/01/20246116
504/02/202411127

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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..

danextian_0-1708125944905.png

danextian_1-1708126299819.png

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors