Tags: EDATE

Flex a Pivot Table to show data for x months ended a certain user defined month

{ 4 Comments }

In this simple 3 column dataset shown below, one can see the month wise demand and energy charge for 2 years – 2017 and 2018. The objective is to compute the month wise demand charge for x months ended a certain user defined Year and Month.  So, if a user selects the Year as 2018, […]

Read More →

Show sales only for corresponding months in prior years

{ 2 Comments }

Refer to this simple Sales dataset The objective is to create a simple matrix with months in the row labels, years in the column labels and sales figures in the value area section.  The twist in the question is that for years prior to the current year (2018 in this dataset), sales should only appear […]

Read More →

Sales data modelling and interactive visualisations

{ 30 Comments }

Visualise Sales Data of a Non-Alcoholic Beverage Company with basic columnar information such as Date of Sale, Time of Sale, Brand, Stock Keeping Unit (SKU), State, City, Quantity sold, Unit Price and Salesman Code.  In this sales dataset, each line item represents one visit for one SKU.  If nothing is sold in a certain visit, then the SKU […]

Read More →

Story telling with Excel Power BI

{ 6 Comments }

With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations.  Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story.  To be able to interact with/create visualizations, […]

Read More →

The DATEDIF() bug

{ 17 Comments }

Assume a beginning date in cell A7 and ending date in cell B7.  In range C7:E7, the task is to compute the years (in cell C7), remaining months (in cell D7) and remaining days (in cell E7).  As an example, if one types in 12-March-2013 in cell A7 and 24-March-2014 in cell B7, then the […]

Read More →