Hi, Good Morning,
I am a beginner in power pivot and I am facing a few problems while working with power-pivot, can anyone pls help...
I am working with 2 data tables - Sales Data Table and Budget Table and these 2 files are linked to a calendar table and location table. I have a Measure SUM of Day Sales, Now I have Power Pivot with financial years (July to June) in column and month as rows.
Question 1
While calculating YTD Sales using below formula it is giving a cumulative results without FY break, in other words August 2016 YTD Sales is coming as a cumulative sales effective July 2013. I am looking for a year-wise totals - August 2016 YTD Sales should add only July 16 and August 16.
DATESYTD (Calculate(total sales),DATESYTD(calendar[dates]))
How do I get yearwise cumulative totals without a continuous total through year 1 to year 4?
Question 2
Daily Sales Table is updated with day wise sales whereas budget sales table is already populated with day- wise budget of July 16-June 17. Budget and Sales Table both are linked to Calendar table.
I have calculated a measure as Sales FY17 (Calculate(Total Sales), Calendar(FY) =2017)).
I have calculated a measure as Budget FY17 (Calculate(Total Sales), Calendar(FY) =2017)).
Created a pivot by taking months from calendar table, 2 measures (as shown above) added in the pivot, result is for Sept, Sales is taking upto 17th Sept Sales and Budget is taking full month sales, how do I get budget data only till 17th Sept for a meaningful comparison?
Regards
SAMIS
I am a beginner in power pivot and I am facing a few problems while working with power-pivot, can anyone pls help...
I am working with 2 data tables - Sales Data Table and Budget Table and these 2 files are linked to a calendar table and location table. I have a Measure SUM of Day Sales, Now I have Power Pivot with financial years (July to June) in column and month as rows.
Question 1
While calculating YTD Sales using below formula it is giving a cumulative results without FY break, in other words August 2016 YTD Sales is coming as a cumulative sales effective July 2013. I am looking for a year-wise totals - August 2016 YTD Sales should add only July 16 and August 16.
DATESYTD (Calculate(total sales),DATESYTD(calendar[dates]))
How do I get yearwise cumulative totals without a continuous total through year 1 to year 4?
Question 2
Daily Sales Table is updated with day wise sales whereas budget sales table is already populated with day- wise budget of July 16-June 17. Budget and Sales Table both are linked to Calendar table.
I have calculated a measure as Sales FY17 (Calculate(Total Sales), Calendar(FY) =2017)).
I have calculated a measure as Budget FY17 (Calculate(Total Sales), Calendar(FY) =2017)).
Created a pivot by taking months from calendar table, 2 measures (as shown above) added in the pivot, result is for Sept, Sales is taking upto 17th Sept Sales and Budget is taking full month sales, how do I get budget data only till 17th Sept for a meaningful comparison?
Regards
SAMIS
