topic Re: Aggregated Total Count as of TODAY() vs Daily Aggregated Count as of TODAY() in DAX Commands and Tips

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

RiskyBiscutsBro — Fri, 06 Sep 2024 23:00:49 GMT

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.

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

lbendlin — Fri, 06 Sep 2024 21:33:46 GMT

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-Forum/ba-p/963216

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/1447523

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

RiskyBiscutsBro — Fri, 06 Sep 2024 22:01:21 GMT

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.

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

lbendlin — Fri, 06 Sep 2024 22:17:49 GMT

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

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

RiskyBiscutsBro — Fri, 06 Sep 2024 22:51:52 GMT

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:

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

lbendlin — Fri, 06 Sep 2024 22:51:23 GMT

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-Forum/ba-p/963216

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

RiskyBiscutsBro — Fri, 06 Sep 2024 22:52:51 GMT

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.

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

RiskyBiscutsBro — Fri, 06 Sep 2024 22:59:54 GMT

This is the anonomized dataset:

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

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

lbendlin — Sat, 07 Sep 2024 20:57:32 GMT

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

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

RiskyBiscutsBro — Mon, 09 Sep 2024 17:41:47 GMT

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.

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

lbendlin — Mon, 09 Sep 2024 17:56:58 GMT

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

RiskyBiscutsBro — Mon, 09 Sep 2024 18:20:28 GMT

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