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

[RiskyBiscutsBro]

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

  

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

 

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

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

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)

Date

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.

[lbendlin · Accepted solution]

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

[RiskyBiscutsBro]

This is the anonomized dataset:

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

[lbendlin · Accepted solution]

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

[RiskyBiscutsBro]

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

[RiskyBiscutsBro]

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

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

[lbendlin · Accepted solution]

[lbendlin]

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

[RiskyBiscutsBro]

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.

[lbendlin]

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

[RiskyBiscutsBro]

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:

[lbendlin]

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

[RiskyBiscutsBro]

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.