MTD Sales by Category Workdays

[clarkpaul]

Our Sales Dept produces a sales ratio calendar that excludes weedends and holidays.
The ratios start small at the beginning of the month and reaches 100% at the end of the month.
The monthly forecast is multiplied by the ratio to track their progress on a daily basis.

My PBI report works fine for an item but has the wrong number for a category:

In the example above the MTD Sales is 137 for 17 Jun but it should be 137 + 83 = 220
This is the link to the PBI Report on OneDrive:

https://1drv.ms/u/s!Aq5dyjpfjB3UgYhu2_W4JQPq4inhJQ?e=jfu2sI

Any help would be greatly appreciated!

[v-yalanwu-msft · Accepted solution]

Hi, @clarkpaul ;

You could modify the MTD Sales column as follows:

MTD Sales2 = 
var _a=CALCULATE(SUM([Sales]),FILTER(ALLEXCEPT('Sales','Sales'[Category]),[Date]<=EARLIER(Sales[Date])))
var _b=CALCULATE(SUM([Sales]),FILTER(ALLEXCEPT('Sales','Sales'[Item]),[Date]<=EARLIER(Sales[Date])))
return IF(CALCULATE(DISTINCTCOUNT([Item]),FILTER(ALL(Sales),[Category]=EARLIER([Category])&&[Date]=EARLIER([Date])))=1,_a,_b)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[clarkpaul]

v-yalanwu-msft,
  I ran into a problem when there is no sales for a date.  The MTD value disappears:

I updated the PBI with the new data:

https://1drv.ms/u/s!Aq5dyjpfjB3UgYhxIrpOVz_n7WzpYQ?e=a8hVTF

[clarkpaul]

Thank you very much!!!

[amitchandak]

@clarkpaul , are you using datesmtd with date table ?

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

[clarkpaul]

Amitchandak,

I added your recommendation as

MTD Sales B = CALCULATE(SUM('Sales'[Sales]),DATESMTD('Date Table'[Date]))

and renamed it to MTD Sales B for comparison.

 Below is what I get for MTD Sales B.  The correct answer should be 220

Thanks