Problems getting total budget using FILTER, SUMX, and DISTINCT

[TReynolds]

Hi,

I'm trying to calculate the total budget for the months prior to a selected date (the measure [date selected]). Here's an example of the data I'm using. 

Date	Product	ProdType	Plant	Budget
2020-09-01	Dogs	Large	Bark	2
2020-09-01	Dogs	Medium	Bark	5
2020-09-01	Dogs	Small	Bark	3
2020-09-01	Cats	Cats	Meow	7
2020-08-02	Dogs	Large	Bark	5
2020-08-02	Dogs	Medium	Bark	10
2020-08-02	Dogs	Small	Bark	3
2020-08-02	Cats	Cats	Meow	10
2020-08-01	Dogs	Large	Bark	5
2020-08-01	Dogs	Medium	Bark	10
2020-08-01	Dogs	Small	Bark	3
2020-08-01	Cats	Cats	Meow	10
2020-07-02	Dogs	Large	Bark	5
2020-07-02	Dogs	Medium	Bark	5
2020-07-02	Dogs	Small	Bark	3
2020-07-02	Cats	Cats	Meow	2
2020-07-01	Dogs	Large	Bark	5
2020-07-01	Dogs	Medium	Bark	5
2020-07-01	Dogs	Small	Bark	3
2020-07-01	Cats	Cats	Meow	2
2020-06-02	Dogs	Large	Bark	5
2020-06-02	Dogs	Medium	Bark	5
2020-06-02	Dogs	Small	Bark	3
2020-06-02	Cats	Cats	Meow	2
2020-06-01	Dogs	Large	Bark	5
2020-06-01	Dogs	Medium	Bark	5
2020-06-01	Dogs	Small	Bark	3
2020-06-01	Cats	Cats	Meow	2

Here's the DAX I am using:

Monthly Buget Sum =
VAR _selectmonth = MONTH([select date])
VAR _previousmonth = _selectmonth - 1
VAR _selectyear = YEAR([select date])
RETURN
CALCULATE(
SUMX( DISTINCT('data table'[Budget]), 'data table'[Budget]),
FILTER(
ALL('data table'),
'data table'[Date].[Date] >= DATE(2020-06-01)
&& 'data table'[Date] <= _previousmonth
&& 'data table'[Plant} = MIN('data table'[Plant])
)
)

If I choose 2020-06-02 for [select date], Monthly Budget Sum returns (blank) for both plants, Bark and Meow. This is expected because there is no month before 06.

If I choose 2020-07-01 for [select date], Monthly Budget Sum returns 13 for Bark and 2 for Meow. This is correct, because the monthly budget for Bark is 3 Small dogs, 5 Medium and 5 Large. The monthly budget for Meow is 2.

Things get weird when I select 2020-08-02. Monthly Budget Sum returns 4 for Meow, which is correct (monthly budget for 06 was 2 and for 07 was 2). For Bark, Monthly Budget Sum returns 23, which is incorrect (monthly budget for 06 was 13 and for 07 was 13). Monthly Budget Sum dropped the 3 (the monthly budget for Small Dogs). 

When I set [select date] to 2020-09-01, Monthly Budget Sum returns 14 for Meow factory (which is correct, 2 + 2 + 10) and 41 for Bark factory (which is incorrect 13 + 10 (the wrong budget) + 18). 

Why does Power BI report a correct monthly budget for 06 and 08 but not for 07 for the factory Bark?

Any help that can be given to solve this issue, or rewrite my DAX (I've enough DAX to be dangerous, but that's it), would be greatly appreciated. 

Also, is there another way to differentiate between categories in FILTER besides using MAX() or MIN()? Without one expression or the other, Monthly Budget Sum returns the summated monthly budgets for all factories and does not break it down by category (i.e., by Bark and Meow).  

Thanks for your help,

T

[amitchandak]

@TReynolds , Have tried it with Time Intelligence and Date table. Also, use Values in place of distinct in Budget Sumx

Budget Measure =SUMX( Values('data table'[Budget]), 'data table'[Budget])

MTD Sales = CALCULATE([Budget Measure],DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE([Budget Measure],DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE([Budget Measure],previousmonth('Date'[Date]))
last MTD (complete) Sales = CALCULATE([Budget Measure],DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE([Budget Measure],previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[smillar]

Why don't you create a calculated table, or have the database query do it?  It would be trivial at the database level.