Wrong Totals & SUMIFS for sum in period between two dates

[vassilev1914]

Dear Community,

I've been struggling for more than a week with the following problem:

I was asked to create a template for our Key Account managers in which they will be able to prepare promotional activities on their own and get actuals by updating a pivot table linked with Sales database. (I am using PowerPivot in Excel)

So, in a nutshell:

I've got a few tables: 

One Fact table (representing the Sales Actuals database):
Year     Month    Date        Product   Store     Volume

2019	1	1.1.2019	Skates	Store A	1000
2019	1	1.1.2019	Scooter	Store A	2000
2019	1	1.1.2019	Bike	Store A	3000
2019	1	1.1.2019	Skates	Store B	667
2019	1	1.1.2019	Scooter	Store B	1333
2019	1	1.1.2019	Bike	Store B	2000
2019	2	1.2.2019	Skates	Store A	1100
2019	2	1.2.2019	Scooter	Store A	2200
2019	2	1.2.2019	Bike	Store A	3300
2019	2	1.2.2019	Skates	Store B	733
2019	2	1.2.2019	Scooter	Store B	1467
2019	2	1.2.2019	Bike	Store B	2200

One Target table where the managers will prepare the activities by setting periods and targets for the stores (last two columns are calculated):

Start                           End                            Store      Product  Target Duration(days)    Period (concatenated)

15.2.2019 г. 0:00:00	31.3.2019 г. 0:00:00	Store A	Skates	1200	44	15.2.2019 г.-31.3.2019 г.
15.2.2019 г. 0:00:00	31.3.2019 г. 0:00:00	Store A	Bike	2900	44	15.2.2019 г.-31.3.2019 г.
15.2.2019 г. 0:00:00	31.3.2019 г. 0:00:00	Store A	Scooter	2000	44	15.2.2019 г.-31.3.2019 г.
1.1.2019 г. 0:00:00	31.3.2019 г. 0:00:00	Store B	Skates	800	89	1.1.2019 г.-31.3.2019 г.
1.1.2019 г. 0:00:00	31.3.2019 г. 0:00:00	Store B	Bike	1800	89	1.1.2019 г.-31.3.2019 г.
1.1.2019 г. 0:00:00	31.3.2019 г. 0:00:00	Store B	Scooter	1100	89	1.1.2019 г.-31.3.2019 г.
1.4.2019 г. 0:00:00	31.8.2019 г. 0:00:00	Store A	Bike	14000	152	1.4.2019 г.-31.8.2019 г.

I also have Date table, Customer table and Product table with the respective unique components.

What I managed to do is the following:

Pivot table where I have the Period of activity, Store, Target Vol, Actuals and Act vs Target

Period	Store	Product	Target Qty	Actual Results	Act vs Target (vol)	Act vs Target %
1.1.2019 г.-31.3.2019 г.	Store B	Bike	1 800	6 100	4 300	238,9%
		Scooter	1 100	4 067	2 967	269,7%
		Skates	800	2 033	1 233	154,2%
1.4.2019 г.-31.8.2019 г.	Store A	Bike	14 000	15 960	1 960	14,0%
15.2.2019 г.-31.3.2019 г.	Store A	Bike	2 900	2 850	-50	-1,7%
		Scooter	2 000	1 900	-100	-5,0%
		Skates	1 200	950	-250	-20,8%
Grand Total			23 800	83 700	59 900	251,7%

Basically everything is as I wanted except the total for the Actuals which is wrong due to the DAX row context. Here you are the formula for the actuals that takes the figures from Fact table:

 CALCULATE(
               SUM(Fact[Volume]);
                          FILTER(Fact;(Fact[Date]>=MIN(Target[Start])));FILTER(Fact;(Fact[Date]<=MAX(Target[End]))))

How the hell am I supposed to fix these totals. Watched tens of videos and read a lot of articles. Nothing worked so far. I tried with workaround by adding calculated column in the Target table that would represent SUMIFS and just calculate the figures for each row but with no success in figuring out the right formula.

Please help...

[v-lili6-msft · Accepted solution]

HI, @vassilev1914 

This is a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

If you still have the problem, please share your sample pbix file for us have a test.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[vassilev1914]

Many thanks for the response. It was useful.

Anyway, I found a workaround by inserting calculated column in the Target Table for getting the figures I need and thus the right totals. Here is the formula:

=CALCULATE(
      SUM(Fact[Volume]);filter(Fact;Fact[Product]=Target[Product]);FILTER(Fact;Fact[Store]=Target[Store]);DATESBETWEEN(Fact[Date];[Start];[End]))

[v-lili6-msft]

hi, @vassilev1914 

It's pleasant that your problem has been solved, please accept the reply as solution, that way, other community members will easily find the solution when they get same issue . Thanks.  

Best Regards,

Lin

[vassilev1914]

Anyone? 😞