Measure for sum of current/previous months values, given that data for several dates are empty.

[mukhammedrakhym]

Hi all!

I have a problem with creating a measure for sum of current/previous months values, given that data for several dates are empty.

The problem is that:

I have two fields: date and sales (let’s take March 2022 and February 2022 for date, so that the current month is March, and the Previous month is february).For example, there are no data for February 2, February 15, March 5, March 22, and March 30.

I need to display the sum of current and last month's sales given the following logic:

As much as the data for 2nd and 15th of February is absent, I need to remove the data from 2nd and 15th March from the sum of sales for March too, and vice versa: as much as the data for 22nd and 30th March is empty, I need to remove the data for 22nd February, and remove the sales for the last day of February from the sum of sales for February. For example, if the data for 31st March is absent too, I remove the data for 28th February from the sum of February one more time. 

The logic is that as much as February ends at 28th day (except leap year), and if the data for 29th, 30th, 31st March is empty, we will subtract from the sum of sales for February the sales for the last day of February each time.

Example of data:

Date	Sales
01.02.2022	21828
02.02.2022
03.02.2022	21065
04.02.2022	26605
05.02.2022	21208
06.02.2022	23532
07.02.2022	21823
08.02.2022	27452
09.02.2022	25026
10.02.2022	23268
11.02.2022	26054
12.02.2022	27018
13.02.2022	20933
14.02.2022	21212
15.02.2022
16.02.2022	25486
17.02.2022	21874
18.02.2022	21804
19.02.2022	20943
20.02.2022	23673
21.02.2022	22704
22.02.2022	23732
23.02.2022	20265
24.02.2022	27169
25.02.2022	20695
26.02.2022	20731
27.02.2022	24032
28.02.2022	20439
01.03.2022	26914
02.03.2022	29276
03.03.2022	23364
04.03.2022	29448
05.03.2022
06.03.2022	24583
07.03.2022	28187
08.03.2022	22803
09.03.2022	21747
10.03.2022	21427
11.03.2022	24909
12.03.2022	24805
13.03.2022	29691
14.03.2022	20450
15.03.2022	20955
16.03.2022	26703
17.03.2022	22442
18.03.2022	26964
19.03.2022	29218
20.03.2022	24604
21.03.2022	29580
22.03.2022
23.03.2022	24476
24.03.2022	29399
25.03.2022	22191
26.03.2022	29890
27.03.2022	28323
28.03.2022	28882
29.03.2022	20000
30.03.2022
31.03.2022	23779

How it actually displays sum of sales for month:

Sales for February

600571

Sales for March

715010

How it should display, according to my logic:

Sales for February

535192

Sales for March

664779

Why it can not be implemented in columns:

1. There is a lot of data that needs to be implemented according to this logic, and they can be in different tables, so creating separate columns will not be efficient.

2. As I said above, different data is contained in different tables that are associated with the date table, Calendar, so when creating columns that contain the date, an error may occur.

Please, contact me if you have questions about it, I know it is a complicated problem.

Thank you!

[v-easonf-msft · Accepted solution]

Hi， @mukhammedrakhym 

You may need to add calculated columns to calculate the "previous month sales" and the "next month sales" for each row.

Then try measure like:

Current month sales = 
VAR currentmonth = 3
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month] = currentmonth
                && 'Table'[Previous month Sales] <> BLANK ()
        )
    )

Sales for last month =
VAR lastmonth = 2
RETURN
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Month] = lastmonth
                && 'Table'[Next month Sales] <> BLANK ()
        )
    )

Best Regards,
Community Support Team _ Eason