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:
What I want to do:
Question:
Thank you for your help in advance!
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:
- Feb'19 Dataset: Has Jan'19 Sales Actuals and Feb'19~Dec'19 Sales Forecast
- Mar'19 Dataset: Has Jan'19 & Feb'19 Sales Actuals and Mar'19~Dec'19 Sales Forecast
What I want to do:
- 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)
- Calculate YTD (Year-to-Date), YTG (Year-to-Go) using DAX to fetch the data for the respective cycles
Question:
- 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.
- 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?
- 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!