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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.