Data transformation (using Formula) in Power pivot for data source from Folder

kalam1989

New Member
Joined
Jun 25, 2015
Messages
5
Background:

I've configured a PowerQuery to automatically fetch a sales and forecast dataset that updates monthly.
So, the dataset gets arranged based on the Source File which gets refreshed on a monthly basis.

Example:

  1. Feb'19 Dataset: Has Jan'19 Sales Actuals and Feb'19~Dec'19 Sales Forecast
  2. Mar'19 Dataset: Has Jan'19 & Feb'19 Sales Actuals and Mar'19~Dec'19 Sales Forecast

What I want to do:


  1. Create each dataset as a cycle in a pivot table (Eg. Feb'19 dataset = Feb cycle with Jan'19~Dec'19 monthly sales figures, Mar'19 dataset = Mar cycle with Jan'19~Dec'19 monthly sales figures)
  2. Calculate YTD (Year-to-Date), YTG (Year-to-Go) using DAX to fetch the data for the respective cycles

Question:


  1. For creating each dataset, is there a way for me to transform the data in Power Pivot? Currently my dataset has no way to categorize by cycles except for the data source field from PowerQuery, but I'm not sure how to utilize this to get my dataset in different cycles for use in Pivot tables. Ultimately, my powerpivot should calculate total year sales as [2019 SALES] based on the selected cycle.
  2. If I write a YTD measure in the DAX measure as <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">YTD Sales:= CALCULATE([2019 SALES],DATESYTD(Calendar_Lookup[Date]))</code>, that would give me a YTD sales number, but again, I want this to be dynamic based on the cycle that I select. How do I make the formula dynamic according to each cycle?
  3. As for the YTG measure, is it correct for me to say that I can calculate using a measure <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">YTG Sales:=CALCULATE([2019 Sales] - [YTD Sales])</code>?

Thank you for your help in advance!
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top