[porter444Participant Started: 10Replies: 24Total: 34]

Thank you Tom.  What I’m really looking for is the net for each day, not each specific transaction.  I’d also like to be able to group the dates by month, quarter, year and see the available inventory balance at the end of the month after all transactions, and the net of all transactions within a given month.

That’s why this is so complex in my mind… the balance forward needs to recalculate based on the filter context for the time period groupings (day, month, etc).

[porter444Participant Started: 10Replies: 24Total: 34]

You could add a “sort by” value in your calendar table.

[porter444Participant Started: 10Replies: 24Total: 34]

What’s the issue with using Power Query to unpivot? – you mentioned it’s not possible?

[porter444Participant Started: 10Replies: 24Total: 34]

Thanks Tom.

I have played around with Power BI, but haven’t yet deployed anything.  I am sure I will at some point soon.

[porter444Participant Started: 10Replies: 24Total: 34]

Thank you sir!

[porter444Participant Started: 10Replies: 24Total: 34]

I added some additional information to this attachment. Hope it clarifies what I am asking.

Attachments:

You must be logged in to view attached files.

[porter444Participant Started: 10Replies: 24Total: 34]

That’s one of the reasons I think this forum is important. Have a great day!

[porter444Participant Started: 10Replies: 24Total: 34]

Thanks Tom, that makes sense to me. I would like to see the solution for “grand total only” if you wouldn’t mind sir.

Thanks in advance!

[porter444Participant Started: 10Replies: 24Total: 34]

In the attached, I added a step to replace “BFREE1” and “BFREE” and then grouped. You could also replace “1” with nothing since only that number (in your example) needs to be removed to have the rep values match.

Attachments:

You must be logged in to view attached files.

[porter444Participant Started: 10Replies: 24Total: 34]

Thanks Tom!  This is working.

I am using Excel 2016.

[porter444Participant Started: 10Replies: 24Total: 34]

AWESOME!!!!!

[porter444Participant Started: 10Replies: 24Total: 34]

I like the measures approach, I think that is the more efficient way to handle.  Thank you for both solutions!  Great learning opportunity.

As I reviewed these I thought of two other potential possibilities using RANKX to rank the dates and then harvest the two largest values, or TOPN may be another approach.  Then maybe using CUBESET to pull the dates and CUBEMEMBER to display the sales values for each.  (I think I saw something like that in the online training course).  Not sure how to write either of these…

[porter444Participant Started: 10Replies: 24Total: 34]

Here is a link to the other thread (has example workbook)

https://forum.powerpivotpro.com/forums/topic/value-change-between-last-two-dates/

[porter444Participant Started: 10Replies: 24Total: 34]

Tom,

I think you are responding to another thread.  I believe it is a relative context.  I say this because the time between dates can be variable, and the actual dates themselves change.

Example:  On Mon of this week I updated my data and the most recent date is 7/18/2016, the previous week I updated my data on 7/11/2016.  The prior week, because of the holiday, I updated data on 7/5/2016.

• Calculation on 7/18 was (7/18 value) –  (7/11 value) = Change since last date
• Calculation on 7/11 was (7/11 value) – (7/5 value) = Change since last date

I really on want the difference between the last two dates.  In the attached you’ll see the value in the purple text box at the corner of the chart.

 

 

Attachments:

You must be logged in to view attached files.

[porter444Participant Started: 10Replies: 24Total: 34]

The second point above (Goal Line), I did find a solution.

Inventory Goal Line:=IF(ISBLANK([Inventory Value]),BLANK(),CALCULATE([Inventory Goal],ALL(‘Calendar'[Date])))

 

[Author]

Posts