Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Cumulative Graph missing Data

Hi PBI Community,   I am trying to graph a cumulative total (# of expirations) by a week ending date.  It appears to be working (mostly) with the exception that if a particular week ending date did...
Need Help
  • speedramps's avatar
    speedramps
    4 years ago

    Hi  again codestule

     

    Please dont take offence to the "not doing your job" comment.

    I copy and paste it it on all my posts because I am an uppaid volunteer who is happy to share skills to people who are keen to learm but some members just want their jobs doing for free. 😀😀😀

     

    Sorry to hear you are still struggling with your cummulative report.

     

    The solution I provided works

    Click here to download and try it 

     

    I have also provide a screen print to show it works.

    In the top monthly blue graph you can see there were no sales in some weeks.

    But the top red line and the bottome blue graph show the cummulative values.

    So I trust you agree that this solution does work, but you may have copied it incorrectly?

     

    Try download the example again and carefully change it to your data, but do not change the logic.

     

    If you still have problems then copy and paste your data into the chat as a text table (not a screen shot) so we can import the data and share the solution.  Remember not to share any private date

     

    Good luck!

     

    I am sure you will master how to produce cumulative reports soon, it it difficult the first time.  😎

     

     

speedramps's avatar
speedramps
Icon for Super User rankSuper User
4 years ago

Click here to download a solution 

 

How is works ...

 

Firstly alway use calendar table for date logic.

Never use the date directly from the fact tables.

Learn abour calendar tables here https://www.youtube.com/watch?v=BtYn1hfdSAM

 

 This dax measure will just get sales for current perid on visual, for example week

Total sales = SUM(Sales[Sales])
 
This DAX measure get the mins and max date for each date point on the graph
If the data point is beyound today then draw nothing, unless you wnat to plot ito the future.
The calaculate comman then overide the default weekly filter and computes all sales upto the end of the week.
 
Cumulative sales =
VAR mymindate = MIN('Calendar'[Date])
VAR mymaxdate = MAX('Calendar'[Date])
VAR today = TODAY()
RETURN
IF(mymindate > today, BLANK(),

CALCULATE(
    [Total sales],
    'Calendar'[Date] <= mymaxdate
))
 
This is the best way to calculate cumulative sales.
Please try change your formulas using the above example.
That is the best way for you to learn DAX, rather than ask me to build you a bespoke solution.
 

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remeber we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you !