Automatically filter dates in vusualisation of cumulative sum over time of quantity

[andrea_chiappo]

I have the following tables: CalendarDates -> DimUsers <-> DimCustomers, 

where the arrows indicate the relationships

The active relationship among them are:

CalendarTable[Date] - DimUsers[createdDate] and DimUsers[userID] - DimCustomers[userID]

The [createdDate] indicates when a User registers to the service.

Following the suggestions at https://www.daxpatterns.com/cumulative-total/ I was able to calculate the 

running sum of the users as they registered over time, using the following formula:

CumulativeUsers = IF(

COUNTROWS(DimUsers)>0,

CALCULATE(

COUNTROWS(DimUsers),

FILTER(ALL(CalendarDimUsers), CalendarDimUsers[Date] <= MAX(CalendarDimUsers[Date]))

),

0

)

The headache now's that, when I choose a specific customer via slicer, this selection does not 

narrow down the range of dates on the visualisation to match the createdDate when a user first 

and last registerd to the service.

In the attached screenshot, the range should be 14/10/2019 and 11/12/2019.

Instead, it always shows the entire range of the createdDate dimension.

Any clue on how to solve this? Many thanks

[v-alq-msft]

Hi, @andrea_chiappo 

You may try the following measure to see if it helps.

CumulativeUsers =
var _date = SELECTEDVALUE(CalendarDimUsers[Date])
return
IF (
    COUNTROWS ( DimUsers ) > 0,
    CALCULATE (
        COUNTROWS ( DimUsers ),
        FILTER (
            ALLSELECTED ( CalendarDimUsers ),
            CalendarDimUsers[Date] <= _date )
        )
    ),
    0
)

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[andrea_chiappo]

It just leads to the same result as my calculation. It does produce the correct cumulative sum, but the date range displayed is still not what I wish for, as it does not narrow down depending on the selection

[Pragati11]

Hi @andrea_chiappo ,

On your visual, you are using a date column. This by default shows YEAR (the highhest in the hierarchy of the date).

In Power BI the dates are by default created in a hierarchy like: Year --> Quarter --> Month -->Day.

On the visual, top right corner, you will see drill-down options. Use it to drill down to your lowest hierarcy, it will show you dates.

Or the other option is - While you dragged DATE column to visual, it dragged the hierechy by default. Right click on DATE column and chnage it to the highlighted as shown below:

Just see if this resolves your issue.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[andrea_chiappo]

Thank you for the quick reply Pragati

I have already checked this but the date column I'm using does not contain a hierarchy. 

I tried creating one, clicking on New Hierachy from the dropdown menu, but result's always the same

[Pragati11]

Hi @andrea_chiappo ,

what is the data-type of your Date column? Are you using Direct Query mode in your model?

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[andrea_chiappo]

the Data-type is Date and I am using the Import mode, not the Direct Query

[Pragati11]

Hi @andrea_chiappo ,

In Power BI a DATE data-type by-default creates an hierarchy as mentioned in my last reply.

Is it enabled on your Power BI deskop. You can check this under

File --> Options and Settings --> Options --> Global --> Data Load --> Auto date/time option

This option should be checked. If this is not checked, modify it to checked. Save your report, reopen it again.

Click on your visual. On the top right, you should see a drill-down icon.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[andrea_chiappo]

yes, the option is checked but, no, it still doesn't automatically identify the date hierarchy 🙄

The Power Bi version is the latest (March 2020)

[Pragati11]

HI @andrea_chiappo ,

Can you share your .pbix file by removing sensitive information?

Thanks,

Pragati