cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ocoder
Helper I
Helper I

Stop showing accumulation in visual after today's date and drill down using accumulated data

Hello,  I'm stumped trying to figure out how to get my visuals to behave as we need.  For referece, please see the attached image.Receipts Visual that needs limitsReceipts Visual that needs limits

 

On the left we have the graph visual which should show received column data only up to the current calendar week.  We're using the standard method to crate this accumulation:

 

 

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

 

However, it looks like this method lacks the ability to click and drill to all of the records that compose that total.  When I click I only get the records that match the current calendar week in the chart on the right.

 

Is there a better way to do accumulation that would allow us to do both of the things I mentioned here?

 

Thanks!

9 REPLIES 9
jstorm
Resolver III
Resolver III

I'm not sure it will resolve your issue, but have you tried using the report-level relative date filter rather than using a relative date filter inside your measure?

@jstorm I have not tried that.  I will look into it though as it sounds like it might be useful in some cases.  My first thought is that the need for the ETA data to continue past the filtering date might cause a complication.

@Ocoder did you tried the measure I proposed?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2kcan you explain what you are accomplishing with your change to the measure?

@jstorm it will not return blank if there is no value and that will stop the curve and this is getting the max performance and this calculation will be sent back to Formula Engine.


@Ocoder  did you tried it?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k I have been quite busy today so I'm still trying to get back to this task.  I will be sure to provide an update when I do.

parry2k
Super User
Super User

@Ocoder update your measure as below

 

Cumulative Quantity :=
CALCULATE (
    SUM ( Transactions[Quantity] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
) *
DIVIDE ( SUM ( Transactions[Quantity] ), SUM ( Transactions[Quantity] ) )

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@parry2k 

 

Your modified code did not do what I was looking for.  The graph visual now shows only the records that occur on each week.  I was looking for a way to accumulate totals until the current week, then cut off the chart so the future weeks show no receipts.  I am looking into a solution based on information from here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Combine-Sales-and-Forecast/td-p/690855

 

I think this is a similar problem to solve, but so far I cannot get step one to work in my project.

I was able to resolve one of the two issues by adapting the instructions from https://community.powerbi.com/t5/Quick-Measures-Gallery/Combine-Sales-and-Forecast/td-p/690855.

 

The other issue  I still need to resolve is getting the records from the accumulated totals to display when a week is selected in the graph.  Currently, it only displays the records that occur in the week selected.  I'll create a separate ticket for that and mark this solved.

 

To summarize my solution for cutting off the graph display:

 

Create a new column in the date dimesion in M script:

 todaydate = Date.From(DateTime.LocalNow()),
    #"InsertIsInPast" = Table.AddColumn(#"PreviousLine", "PastWeeks", each if List.AllTrue({Number.FromText([Year]) <= Date.Year(todaydate), [Week of Year] < Date.WeekOfYear(todaydate)}) then "Yes" else "No")

 

Then create a new measure which gets the last date which qualifies as being in the past:

Max Past = CALCULATE(
    MAX('Date Dimension'[Date]),
    FILTER(
        ALL('Date Dimension'),
        'Date Dimension'[PastWeeks] = "Yes"
    )
)

 

Then create another ne wmeasure which checks against "Max Past":

Cume Rec with Current Date Cutoff = 
var curdate = MAX('Date Dimension'[Date])
return SWITCH(
    TRUE(),
    curdate <= [Max Past],[Received_Accumulated],
    curdate > [Max Past], BLANK())

 

Result:

Annotation 2020-05-05 153446.png

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors