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
RiskyBiscutsBro
Frequent Visitor

Aggregated Total Count as of TODAY() vs Daily Aggregated Count as of TODAY()

I am having a DAX measure issue, I am trying to do a burndown style line chart, but I need to be able to use the built in forecast function, but I am having a issues with the overall total of my task counts, i need to be able to provide a cut off date of TODAY().

 

So I am getting the first chart, but I need the second. I had no issue with the first measure that provides the cut off, but its the max total I cant seem to get to work.

 

What I get with my two current measures (presented below):

RiskyBiscutsBro_5-1725642985465.png  

What I need the chart to look like, so anything after TODAY() does not present itself, like the example below:

 RiskyBiscutsBro_4-1725642945205.png

In table format you can clearly see it stops at Setp 6, but the CountFinal goes all the way to Dec 31, 2025.

RiskyBiscutsBro_0-1725644650882.png

 

This is my basic Many-to-One, single direction relationship, 'Done (ISSUE Key)'[LAST_TRANSITION_TO_STATUS] --> 'Date'[Date]

 

RiskyBiscutsBro_1-1725642871635.png

and this is the basic table structure for both tables:

 

Link to Data: 

https://docs.google.com/spreadsheets/d/1L62DKhRGtOBVoKuvsn_cmjNU0E2BKmL-SKE_ipbTqyo/edit?usp=sharing

 

Done (ISSUE Key)

RiskyBiscutsBro_2-1725642892102.png

 

Date

RiskyBiscutsBro_3-1725642916695.png

 

This is the measure that works as it should, it gives me an agregated issue count as time goes by.

*TotalDistinctDoneIssueKeyCountToday =
CALCULATE (
    DISTINCTCOUNT('Done (ISSUE Key)'[ISSUE_KEY]),
    'Done (ISSUE Key)'[ISSUE_STATUS_NAME] IN {"Done", "Cancelled", "Closed"},
    FILTER(
        ALLSELECTED('Done (ISSUE Key)'),
        'Done (ISSUE Key)'[LAST_TRANSITION_TO_STATUS] <= MAXX(FILTER('Date', 'Date'[Date] <= TODAY()), 'Date'[Date])
    )
)

 

This is the measure I cant seem to get to work, ive tried many variations, this is the closest I can get to it.

*TotalDistinctDoneIssueKeyCountFinal =
VAR MaxDate = MAX('Date'[Date])
RETURN
IF(MaxDate <= TODAY(),
    CALCULATE (
        DISTINCTCOUNT('Done (ISSUE Key)'[ISSUE_KEY]),
        'Done (ISSUE Key)'[ISSUE_STATUS_NAME] IN {"Done", "Cancelled", "Closed"},
        FILTER(
            ALLSELECTED('Done (ISSUE Key)'),
            'Done (ISSUE Key)'[LAST_TRANSITION_TO_STATUS] <= TODAY()
        )
    ),
    BLANK()
)

 

Do I need to add another unrelated date table, is my measure missing something? I do not want to use a visual filter for TODAY() as end date, as this will prevent me from using the forecast function. 

 

Any suggestions will be most appreciated.

2 ACCEPTED SOLUTIONS

lbendlin_0-1725742391038.pnglbendlin_1-1725742585246.png

Set the x axis maximum to 12/31/2025.

 

 

View solution in original post

11 REPLIES 11
RiskyBiscutsBro
Frequent Visitor

lbendlin_0-1725742391038.pnglbendlin_1-1725742585246.png

Set the x axis maximum to 12/31/2025.

 

 

It was that simple hey? Thank you!! Ive been at that for days!

@lbendlin thanks for providing a PBI file, unfortunately this does not solve my problem. I may not have articulated it properly, and may not have used the best images.

but my issue is strictly with this measure:

*TotalDistinctDoneIssueKeyCountFinal =
VAR MaxDate = MAX('Date'[Date])
RETURN
IF(MaxDate <= TODAY(),
    CALCULATE (
        DISTINCTCOUNT('Done (ISSUE Key)'[ISSUE_KEY]),
        'Done (ISSUE Key)'[ISSUE_STATUS_NAME] IN {"Done", "Cancelled", "Closed"},
        FILTER(
            ALLSELECTED('Done (ISSUE Key)'),
            'Done (ISSUE Key)'[LAST_TRANSITION_TO_STATUS] <= TODAY()
        )
    ),
    BLANK()
)

 I need a to have a flat line of the max count from when it starts to TODAY().  So it needs to look like the below.

RiskyBiscutsBro_0-1725903655320.png

I do appreciate you taking the time on the weekend to look at it.

lbendlin_0-1725904604241.png

 

lbendlin
Super User
Super User

As you already showed, returning BLANK will suppress the part of the chart that you don't want.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

OOpps i should of put headers on the image, no the formula does not give me what I want. I edited the chart on paint so people could see what I want. Ill edit my question so its clearer.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

I cant submit the tables in the orginal post, its keeps saying I have over 20K characters, which is not the case. But see below:

 

Thank you,  but I saw your ealier link, and it gives me that metioned error. Ill see if I can post the date in a URL instead.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.