topic Re: Cumulative chart with empty dates in DAX Commands and Tips

Cumulative chart with empty dates

Cienfu — Thu, 19 Mar 2020 15:48:10 GMT

Hello,

I'm trying to create a commulative graph where I would indicate the evolution of a process consisting in requesting an item, approve the item, and then execute the action.

The idea would be that the first day only one item has been requested, but nothing yet approved or executed.

By day 6 for example, 6 have been requested, 4 approved and 2 executed.

By day 10, 10 requested, 7 approved 4 executed.

Item	Date requested	Date approved	Date executed
Item 1	01/01/2020	05/01/2020	06/01/2020
Item 2	02/01/2020	05/01/2020	06/01/2020
Item 3	03/01/2020	05/01/2020	08/01/2020
Item 4	04/01/2020	05/01/2020	09/01/2020
Item 5	05/01/2020	07/01/2020	12/01/2020
Item 6	06/01/2020	07/01/2020
Item 7	07/01/2020	09/01/2020
Item 8	08/01/2020
Item 9	09/01/2020
Item 10	10/01/2020

I have tried with the formula below, but the result was not correct.

Cummulative items approved= CALCULATE(
COUNTA(Data[Item]),
FILTER(
ALL(Data),
Data[Date approved]<=MAX(Data[Date approved])))

Would you know what am I missing?

Thanks a lot!

Re: Cumulative chart with empty dates

HotChilli — Thu, 19 Mar 2020 16:51:20 GMT

When you say 'the result was not correct', do you mean the rows with no approved date were included in the count?

If so, you can add a clause :

&& NOT(ISBLANK(Data[Date approved])

Re: Cumulative chart with empty dates

Cienfu — Thu, 19 Mar 2020 18:24:10 GMT

Thanks! it works fine individually:

If I create a graphic for each measure (axis X date requested/approved/converted and Y number of items it shows correct data.

However, when I try to combine them into just one graph, the result is totally messed up. I think it's because of the dates, since some dates are existing in one column but not in others

Re: Cumulative chart with empty dates

Anonymous — Sat, 21 Mar 2020 11:14:00 GMT

If you follow the rules of dimensional design, you'll have no problems creating correct and simple measures. Your visuals will work correctly as well.

Please watch this for a start:
https://www.youtube.com/watch?v=78d6mwR8GtA&t=1247s

Best
D