Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Matjo
Advocate I
Advocate I

Date hierarchy breaks DAX measure

I use the following measure to calculate a cumulative sum: 

Cumul = 
CALCULATE (
    SUM ('Query'[Indicator]),
    FILTER (ALL ('Query'[Date] ),'Query'[Date] <= MAX ( 'Query'[Date]))
)

On this dataset: 

DatePersonsIndicator
01/02/2017Mick1
01/03/2017Mick1
01/04/2017Mick1
01/02/2017David1
02/02/2017David1
03/02/2017David1
03/02/2017Mick1

 

It works properly when the date is a date. If I change date to Date Hiearchy the measure stops working. 
image.png

Is this working as designed? If so, could someone please explain how the context works for me. 

Best regards,
Mattias

 

1 ACCEPTED SOLUTION
RolandsP
Resolver IV
Resolver IV

Do you use Date table in your data model?

View solution in original post

4 REPLIES 4
RolandsP
Resolver IV
Resolver IV

Do you use Date table in your data model?

Hi, 

 

no I do not use a date table. This is just a simple example I did to try and understand how dates/date hieararchy works. 

Hi, I created and added a date dimension and now both work as intended. I still do not understand why the date dimension is required or why the date hierarchy breaks the DAX function but I will includ a date dimension in all the things I do now. 

Anonymous
Not applicable

 


@Matjo wrote:

Hi, I created and added a date dimension and now both work as intended. I still do not understand why the date dimension is required or why the date hierarchy breaks the DAX function but I will includ a date dimension in all the things I do now. 



I've just stumbled across this thread and seeminly have an identical issue to the one you listed, choosing date hierarchy breaks my cumulative running totals.

Are you able to elaborate on your solution of adding a date dimension. Was that creating a new date table and then adding a relationship against your existing dates?

Thanks,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.