Cumulative Graph missing Data

[Anonymous]

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 didn't have any expirations it doesn't show any data (see pic below).  I would like it to show the previous weeks data but after browsing the forum I haven't been able to figure out how to adapt solutions to similar problems I've found.  Any all help anyone could provide would be greatly appreciated.

Output:

The Measure I am graphing is as follows:

zE1CumulativeExpiredStation =

VAR selectedStation = SELECTEDVALUE('TABLE'[STATION])

Return CALCULATE('TABLE'[zE1Tot],

FILTER(ALL('TABLE'),

'TABLE'[STATION] = selectedStation),

FILTER(ALL('TABLE'),

'TABLE'[weekEndingOriginalExpire] <= MAX('TABLE'[Expire Date])),

FILTER(ALL('TABLE'),

OR('TABLE'[INSPECTED] > MAX('TABLE'[weekEndingOriginalExpire]), 'TABLE'[INSPECTED] = BLANK()))

)

[zE1Tot] = SUM(zE1) <--- this is a measure

zE1 = IF(OR('TABLE'[Status] = "Expired", 'TABLE'[Status] = "Completed after expiration"), 1, 0)  <--- This is a calculated column in the table

[Anonymous]

@speedramps ,

I give you kudos for your time/effort and very much appreciate it.  Contrary to what was said in your post, I'm not looking for (nor do I expect) someone to give me the solution.  I've attempted to adapt your proposal and I get the same results I already have, it calculates the cumulative total just fine except if a given weekending doesn't have any data it still displays 0 on the graph (this is both with the use of a separate date table and w/o).

Again, thank you for your time and effort and I do sincerely appreciate it however at this time I'm still not able to accomplish what I'm seeking out to do with your proposal.

[speedramps · Accepted solution]

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

[Anonymous]

@speedramps ,

My apologies for how I interpreted your original response, thanks for getting back to me again.  Unfortunately I am unable to download the file you shared, I attempted to use both my work and personal one drive account and in both cases it says there was an "error" and failed to download.  I have updated my dax as follows:

zTESTcumExpSt =

VAR myMinDate = MIN('date_calendar (2)'[WeekEnding])

VAR myMaxDate = MAX('date_calendar (2)'[WeekEnding])

VAR tDate = TODAY()

VAR selectedStation = SELECTEDVALUE('met_mac (2)'[STATION])

RETURN

IF(myMinDate > tDate, BLANK(),

CALCULATE('met_mac (2)'[zE1Tot],

FILTER(ALL('met_mac (2)'),

'met_mac (2)'[STATION] = selectedStation),

FILTER(ALL('met_mac (2)'),

'met_mac (2)'[weekEndingOriginalExpire] <= MAX('date_calendar (2)'[WeekEnding])),

FILTER(ALL('met_mac (2)'),

OR('met_mac (2)'[INSPECTED] > MAX('met_mac (2)'[weekEndingOriginalExpire]), 'met_mac (2)'[INSPECTED] = BLANK())),

'date_calendar (2)'[WeekEnding] <= myMaxDate

))

Upon doing this my graph still appears to not show the cumulative for certain week ending criteria even when using a separate date table as you have suggested:

None the less, I don't doubt your proposed solution works.  I feel like it has to be something in the filtering/slicing on my end but it is driving me crazy trying to figure it out.

[speedramps]

Hi agian codestule

No appology necessary, but thank you and thanks for the kudos.

Well done on developing a solution. 😀

I have refeshed the link, so you can try download my example again.

It is on Onedrive, so you shoud be able to access it

https://1drv.ms/u/s!AgMTUY3Uvq3bg6lXcgsLMa0rZtCRGA?e=0O3Rov

Good luck!

If you need more help then raise a new tickey and quote @speedramps anywhere in the text and I will receive an automated notification, but another super use might get to you first. 😎

[speedramps]

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 !