TOTAL is correct but rows are missing

0
votes

I am stuck with a problem which I am not able to solve. It is About MS PowerBI. I have the following Data (everything in one Table):

  • Product_ID
  • Production_date
  • Sales_date
  • Amount Sales (which is for every row in the table "1" -->because every product in the table was sold)

And the following two Measures:

  • Sales_amount_thisMonth

  • Sales_amount_lastMonth

  • EndofMonth (which gives me the last date within the next 3 months)

For every Product_ID there is a Production_date and a Sales_date. In my Measures I calculate the Amount of Product_IDs with 2 given DATESBETWEEN()-Filters. For Sales_amount_thisMonth:

  • Sales_amount_thisMonth = CALCULATE(SUM('Products'[Amount Sales]);DATESBETWEEN('Products'[Sales_date];STARTOFMONTH('Products'[Production_date]);ENDOFMONTH('Products'[Production_date]))

And for Sales_amount_lastMonth:

  • Sales_amount_thisMonth = CALCULATE(SUM('Products'[Amount Sales]); DATESBETWEEN('Products'[Production_date];STARTOFMONTH(PREVIOUSMONTH('Products'[Production_date]));ENDOFMONTH(PREVIOUSMONTH('Products'[Production_date]))); DATESBETWEEN('Products'[Sales_date];STARTOFMONTH(PREVIOUSMONTH('Products'[Production_date]));[EndofMonth]))

For example I want to know which Product_IDs were produced in April AND sold between April and July and I want to know which products where produced in May AND sold between May and August (this is what the 2 Measures should do). I need those two numbers for comparison for the given month. Now i want to display both Measures in a Report as follows: my Report in PowerBi So as you can see, I can adjust the slider and select the month I want to compare to the previous month.

Now to my PROBLEM:
Both Totals are correct; there were 188 sold Product_IDs which where produced in April and the Sales_date is between April and July and there where 313 sold ProductIDs which were produced in May and sold between May and August. And the rows for the Sales_amount_thisMonth are all correct, BUT there are rows missing for Sales_amount_lastMonth. So the Total is correct, but I am not shown all of my Sales-amount_latMonth. I thought that the Problem was between the selected month in the slider and the month in the DATESBETWEEN()-filter. But as it turns out that is not the case.

So I could really use some help here. Could somebody please give me some insight on how to fix this issue or where the problem could be?

1
powerbidax

1 Answers

0
votes

The datesbetween function creates a table with dates between which then it's possible that it can be filtering some records based on the combination of your product and sales (hard to tell without looking at the full data).

You can achieve the same result by using the EOMONTH function and simple comparisons:

Sales_amount_thisMonth = 
//on each row this will be only one value, but on the total you'll have more
VAR productionDate = LASTDATE('Products'[Production_date]) 

RETURN CALCULATE(
  SUM('Products'[Amount Sales]),
   //greater than last day of the previous month (-1) 
   //so that puts you in all cases for current month
  'Products'[Sales_date] > EOMONTH(productionDate,-1)
  && 'Products'[Sales_date] <= EOMONTH(productionDate,0))

You can chose the interval you need by manipulating the second parameter of the EOMONTH function.