topic Re: Missing 1 day in calculation results in DAX Commands and Tips

Missing 1 day in calculation results

Anonymous — Thu, 23 Mar 2023 16:12:48 GMT

Hello,

I prepared DAX calculation which generate number of unique alerts for from 01.02.23 to 28.02.23:

Alerts Generated = CALCULATE(DISTINCTCOUNT('Alert Data'[Alert ID]), FILTER('Alert Data','Alert Data'[Creation Date]>= MAX(START_DATE[START_DATE]) &&'Alert Data'[Creation Date] <= MAX(END_DATE[END_DATE])))

I would like to have number of alerts for each day of February in 1 line chart. All looks good but when I put measure into line chart value I am missing 28.02 alerts.

START_DATE[START_DATE] is a parameter from powerquery: 01-02-2023

END_DATE[END_DATE]) is parameter 28-02-2023

'Alert Data'[Creation Date] and START_DATE[START_DATE] / END_DATE[END_DATE]) have the same date format.

And ofc. I have data for 'Alert Data'[Creation Date] from 28.02.2023

I have no idea why I am missing it on chart.

Thank you for your help.

Re: Missing 1 day in calculation results

andhiii079845 — Thu, 23 Mar 2023 19:01:16 GMT

Do you have data from the 'Alert Data' for the 28.02 ? Can you check it please?

Re: Missing 1 day in calculation results

Anonymous — Fri, 24 Mar 2023 07:42:31 GMT

Yes, I have 'Alert Data' data for 28.02

Re: Missing 1 day in calculation results

andhiii079845 — Fri, 24 Mar 2023 12:10:21 GMT

It is diffcult or not clear where the problem is. Perhaps you are able to share the PBI?

In general check if the visual has a filter directly or from a slicer? Change the graph to matrix and check if you can the data from the missing date.
I only see one thing:
CALCULATE(DISTINCTCOUNT('Alert Data'[Alert ID]), FILTER('Alert Data','Alert Data'[Creation Date]>= MAX(START_DATE[START_DATE]) &&'Alert Data'[Creation Date] <= MAX(END_DATE[END_DATE])))

-> ALL()

CALCULATE(DISTINCTCOUNT('Alert Data'[Alert ID]), FILTER(ALL('Alert Data'),'Alert Data'[Creation Date]>= MAX(START_DATE[START_DATE]) &&'Alert Data'[Creation Date] <= MAX(END_DATE[END_DATE])))

Re: Missing 1 day in calculation results

Anonymous — Fri, 24 Mar 2023 14:26:42 GMT

Thank you for your reply,

Sorry, I can not share powerbi file because it contains sentisive data.

Unfortunately query with ALL() also does not work. The only thing which helps is removing part: <= (END_DATE[END_DATE])

So when I have query like: CALCULATE(DISTINCTCOUNT('Alert Data'[Alert ID]), FILTER('Alert Data','Alert Data'[Creation Date]>= MAX(START_DATE[START_DATE]) ))

then data for 28.02 is visible.

But it is really weird why it happens... And I would like to find solution to make date limitation.