Cumulative count with independent date tables

DAX DAX Calculations
1

Hi everyone,

my client requested a change in using a date filter so that they can have the choice to choose the start date and end date.
With the help of the community, I created two independent date tables and added a filter to the measures to get the date from like:

Measure =
CALCULATE(( [measure1]),
FILTER(‘Date’,
‘Date’[YearMonth] >= SELECTEDVALUE(start_date[start_year_month])
&&
‘Date’[YearMonth] <= SELECTEDVALUE(end_date[end_year_month]))
)

So I am transforming all the measures to reflect the new date filters. But I got some difficulties applying this to the cumulative count measure:

This is the original cumulative count measure:

Registration (Cumumlative) =
CALCULATE(
DISTINCTCOUNT(User[user id],
FILTER( ALLSELECTED('Date'),
'Date'[Date] <=MAX('Date'[Date]) )
)

I am trying to get the cumulative count using the new date filters where there are two independent date tables one for start date and another for an end date.

CumulativeRegistration_considering_date_filters = 
    CALCULATE(
        DISTINCTCOUNT(User[user id]),
    FILTER(
        ALLSELECTED('Date'),
        'date'[YearMonth] >= SELECTEDVALUE(start_date[start_year_month])
        &&
        'date'[YearMonth] >= SELECTEDVALUE(end_date[end_year_month])
    )
)

Can anyone help me rewrite this measure to get the cumulative count?

Thank you all in advance!

2

@iasma ,

If I understand your requirement properly, you don’t need to do any of this – you can just use a single date table connected to the fact table, use the measure you already have for cumulative count, and then just add a date slider to choose and filter the start and end dates.

Cumul Dist Cust = 

CALCULATE(
    DISTINCTCOUNT( Sales[Customer Name Index] ),
    FILTER(
        ALLSELECTED( Dates ),
        Dates[Date] <= MAX( Dates[Date] )
    )
)

image
image555×578 15.6 KB

I hope this is helpful. Full solution file attached below.

– Brian
e DNA Forum – Cumulative Distinct Count with Start and End Dates Solution.pbix (365.2 KB)

3

Thank you @BrianJ
The requirement is to have two dates filters. so I am trying to achieve the same result of the cumulative count but using two dates filters.

So basically I am trying to understand how can we get the cumulative count if we have two independents date tables one for the start of the month and there other for the end of the month.

4

Having two dates…wouldn’t it take up a lot extra data space and processing timing to refreshing your reports. I can also see administration night mare of keeping both date table up to date too. If you have an issues you wouldn’t know which date table is giving you issues.

If you have slicer your user still be able see the dates they choose.

I personally wouldn’t go with two dates tables

that’s my opinion
Keith

5

@iasma ,

See if this does the trick. Still one date table, but two separate slicer/filters – an after and a before.

If this still doesn’t meet your requirement, please provide a mockup of what you want the final result to look like.

Thanks.

  • Brian

image
image571×551 17.2 KB

e DNA Forum – Cumulative Distinct Count with Start and End Dates Solution 2.pbix (365.8 KB)

6

Hi @BrianJ

I was wondering how you not show the second date in the “start date slicer” and not show the first date in the “ending date slicer”. I downloaded the file and i was trying to find it but couldn’t find it.

thanks in advance.
Keith

7

@Keith,

LOL. I just threw a borderless blank button over the top of each of them – the Power BI equivalent of sticking a piece of black electrical tape on your dashboard to cover up the check engine light…

– Brian

8

@BrianJ
ok. I thought there might be away of doing with a formatting feature. sorry for asking the question

thanks
Keith

9

@Keith ,

No, perfectly good question, I was just laughing at the kludginess of the approach.

You can adjust all the format aspects of the date inputs, but not each one independently. Thus, the blank buttons…

  • Brian
10

Thanks a lot for trying @BrianJ
I have attached a mock up of what I am trying to achieve from the same example you provided.

I’ve created two independent date tables and used Total Sales measure as an example of what I what to achieve but with the cumulative count.

Can we get the cumulative count with these tables?
e DNA Forum – Cumulative Distinct Count with Start and End Dates Solution.pbix (383.4 KB)

I appreciate your help!

11

@iasma The data that you uploaded is it confidential data?

12

No, its not.

13

@iasma ,

Okay, thanks for the clarification – this should do the trick for you.

Cumul Dist Sales = 

VAR vTable =
    FILTER (
        ALL ( Dates ),
        Dates[MonthnYear]
            >= SELECTEDVALUE ( start_date[MonthnYear] )
            && Dates[MonthnYear]
                <= SELECTEDVALUE ( end_date[MonthnYear] )
    )
VAR Result =
    CALCULATE (
        [Total Sales],
        FILTER (
            vTable,
            Dates[MonthnYear]
                <= MAX ( Dates[MonthnYear] )
        )
    )
RETURN
    IF(
        SELECTEDVALUE( Dates[MonthnYear] ) <= SELECTEDVALUE( end_date[MonthnYear] ),
        Result,
        BLANK()
    )

image
image442×595 26.7 KB

14

Thanks a lot @BrianJ

There something wrong with the file, can you reupload it?

file
file1917×828 104 KB

16

@iasma ,

Sorry – somehow the file got corrupted. I’m going to have to rebuild it later today and send it to you this evening.

  • Brian
1 Like
17

@iasma ,

Not sure what happened, but I rebuilt the file and I think it’s working just fine now.

I hope this is helpful to you. Full solution file posted below.

– Brian

e DNA Forum – Cumulative Sales with Start and End Dates Solution 4.pbix (386.1 KB)

1 Like
18

Thank you for the effort @BrianJ .

My question here, what is the purpose of using the Monthnyear column that ends with 00?
And what were the steps to create it?
I count apply the solution yet.

19

@iasma ,

I always use @Melissa’s awesome Extended Date Table as the date table in my models, and Monthnyear is just the column she includes as the numeric equivalent of the Month & Year column. I’m honestly not sure why she includes the two trailing zeroes - perhaps just to maintain consistency with the DateInt field. Your calculated column version of that field without the zeroes should work equally well.

  • Brian
1 Like
20

You’re spot on @BrianJ, it’s to mantain a fixed pattern and results in equal string length for these type of fields, although the trailing zero’s could be considered redundant, it doesn’t affect cardinality.

2 Likes