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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
nhoff
Advocate I
Advocate I

Cumulative total - but only until MAX(sales date)

I have created a measure to display cumulative total using the guidelines on daxpatterns.com.

 

Measure:

Sales Acc = CALCULATE(Sales[SalesAmount];FILTER(ALL(Dates);Dates[Date]<=MAX(Dates[Date])))

 

My date table goes beyond the last sales date leaving me with a simple line chart, that visually doesn't work.

 

The question is:

How do I change the measure so I can get picture A instead of picture B? I would prefer not to set the filter manually on the visual, but let it be dynamic.

 

Data modelData modelPicture APicture APicture BPicture B

 

 

 

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
Employee

@nhoff,

 

In your scenario, since your date table goes beyond the last sales date, so some date have no sales. So your get Picture B. If you want the expected reults, you need to filter out the dates which have no sales.

You need to create a column in your date table to check if the current date is before than the max date in your sales date.

Column = IF('Date'[DateKey]<MAX(Sales[DateID]),1,0)

Then add this column to your visual filter.
Capture.PNG

 

Regards,

Charlie Liao

View solution in original post

2 REPLIES 2
MarkS
Resolver IV
Resolver IV

Hi,

I think that instead of this

Sales Acc = CALCULATE(Sales[SalesAmount];FILTER(ALL(Dates);Dates[Date]<=MAX(Dates[Date])))

 

it should be

Sales Acc = CALCULATE(Sales[SalesAmount];FILTER(ALL(Dates);Dates[Date]<=MAX(Sales[SalesDate])))

 

you want the MAX of the SalesDate from the Sales Table.

v-caliao-msft
Employee
Employee

@nhoff,

 

In your scenario, since your date table goes beyond the last sales date, so some date have no sales. So your get Picture B. If you want the expected reults, you need to filter out the dates which have no sales.

You need to create a column in your date table to check if the current date is before than the max date in your sales date.

Column = IF('Date'[DateKey]<MAX(Sales[DateID]),1,0)

Then add this column to your visual filter.
Capture.PNG

 

Regards,

Charlie Liao

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.