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

[Ocoder]

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 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!

[jstorm]

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?

[Ocoder]

@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.

[parry2k]

@Ocoder did you tried the measure I proposed?

[jstorm]

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

[parry2k]

@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?

[Ocoder]

@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]

@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!

[Ocoder]

@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.

[Ocoder]

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: