Carrying forward the MTD sales to next working day when 0 sales for the Working Day

[Anonymous]

Hi,

I have a measure that calculates the MTD sales using:

MTD Batch Sales = if(ISBLANK(SUM('Sales Report incl  Batch'[Qty Shipped])),BLANK(),CALCULATE(

    SUM('Sales Report incl  Batch'[Qty Shipped]),

    FILTER(

        ALLSELECTED('Working Day'[Cumulative Days]),

        ISONORAFTER('Working Day'[Cumulative Days], MAX('Working Day'[Cumulative Days]), DESC)

    )

))

This works in theory to output the total sales up to the latest working day. However, on certain days, when there are no sales, the output is a blank for that working day. Example for Working Day 2 and 5 below, there were no sales on that day and a blank appeared. 

I would like to ouput max sales up to that day, so that Day 2 would be 28 and and Day 5 would be 57, taking the previous days' max sales. I assume it's replacing the "Blank()" in the IF statement's true condition, but have tweaked around with a -1 day but either my syntax and/or methodology was wrong. Please can someone help how to fix this. Thanks.

[Anonymous]

@Ashish_Mathur  Thanks for your contribution on this thread.

Hi @Anonymous ,

Base on your description, it seems that you want to update with the max sales in previous culmulative days if the sales in that day is blank. Am I right? Could you please provide some sample data in the table 'Working Day' and 'Sales Report incl  Batch' with Text format and your expected result with backend logic and special examples? By the way, is there any relationship between these two tables? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And it is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Best Regards

[Ashish_Mathur]

Hi,

I do not understand the Cumulative Days field.  That should be a proper date column/field from the Calendar Table.