Counting the number of days

[Foton83]

Hello everybody,

I have a problem and I can't find a solution anywhere and I will ask for your help. The problem is that I have data where the movement of goods in the warehouse is shown. In one column for each day is the output of the goods and in another column is the input. I created a column that counts the balance for each day, that is, the difference for input and output. From this data, I created a measure that sums up this balance and, in the context of the commodity and date, shows the state of the commodity for the day. Unfortunately, I don't know how to create a measure that would show the number of days on which the stock of goods was zero.

[Foton83]

Hi Everyone,

 I found solution:

https://archerpoint.com/how-to-make-measures-total-correctly-in-power-bi-tables/ 

[v-shex-msft]

HI @Foton83,

You can add a variable table use SUMMARIZE function with category field and custom field to calculate cumulative stock amount based on date and category group. Then you can use iterator function COUNTX to filter and count the row with zero stock.

Measure Totals, The Final Word 
Regards,

Xiaoxin Sheng

[Foton83]

Hi @v-shex-msft ,

Thank you for your help. Unfortunately I tried your solution, but I am running out of memory on my computer. I wrote this measure that creates a new table, but unfortunately it doesn't work:

 New_table = SUMMARIZE('TABLE_WITH_DATA', TABLE_WITH_DATA[Art], TABLE_WITH_DATA[date], "stock", CALCULATE(SUM(TABLE_WITH_DATA[difference], FILTER(ALL(TABLE_WITH_DATA), TABLE_WITH_DATA[date] <= MAX(TABLE_WITH_DATA[date])).

[v-shex-msft]

HI @Foton83,

Perhaps you can try to use the following measure formula if helps:

formula=
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'TABLE_WITH_DATA' ),
        TABLE_WITH_DATA[Art],
        TABLE_WITH_DATA[date],
        "stock",
            CALCULATE (
                SUM ( TABLE_WITH_DATA[difference] ),
                FILTER (
                    ALLSELECTED ( TABLE_WITH_DATA ),
                    TABLE_WITH_DATA[date] <= EARLIEST ( TABLE_WITH_DATA[date] )
                )
            )
    )
RETURN
    COUNTX ( FILTER ( summary, [stock] = 0 ), [date] )

If the above expression also doesn't help, please share some dummy data paste here with table format to help us clarify your structure and test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Foton83]

Hi @v-shex-msft ,

Unfortunately, this fortmule also does not work. I can't add anything as an argument to the "Earliest" function. Power BI displays a message that "the Expression specified in the query is not a valid table expression". As you wrote I paste the table with the data:

Art	Date	InQty	OutQty	Difference
35174	31.10.2019	1969	0	1969
35174	04.11.2019	0	16	-16
35174	05.11.2019	0	4	-4
35174	06.11.2019	0	1	-1
35174	07.11.2019	0	16	-16
35174	08.11.2019	0	10	-10
35174	12.11.2019	0	11	-11
35174	13.11.2019	0	36	-36
35174	14.11.2019	0	22	-22
35174	15.11.2019	56	115	-59
35174	18.11.2019	0	6	-6
35174	19.11.2019	0	5	-5
35174	20.11.2019	0	12	-12
35174	21.11.2019	0	6	-6
35174	22.11.2019	0	17	-17
35174	25.11.2019	0	9	-9
35174	26.11.2019	0	13	-13
35174	27.11.2019	0	8	-8
35174	28.11.2019	0	23	-23
35174	29.11.2019	0	5	-5
35174	02.12.2019	0	6	-6
35174	03.12.2019	0	130	-130
35174	04.12.2019	0	8	-8
35174	05.12.2019	0	17	-17
35174	06.12.2019	0	30	-30
35174	09.12.2019	0	1	-1
35174	10.12.2019	0	14	-14
35174	11.12.2019	0	55	-55
35174	12.12.2019	0	52	-52
35174	13.12.2019	0	7	-7
35174	16.12.2019	0	7	-7
35174	17.12.2019	0	3	-3
35174	18.12.2019	0	13	-13
35174	19.12.2019	0	1	-1
35174	20.12.2019	1	4	-3
35174	23.12.2019	0	5	-5
35174	27.12.2019	4	3	1
35174	30.12.2019	0	1	-1
35174	02.01.2020	2	37	-35
35174	03.01.2020	0	4	-4
35174	07.01.2020	0	9	-9
35174	08.01.2020	0	272	-272
35174	09.01.2020	0	5	-5
35174	10.01.2020	0	4	-4
35174	13.01.2020	0	8	-8
35174	14.01.2020	0	6	-6
35174	15.01.2020	0	6	-6
35174	16.01.2020	0	9	-9
35174	17.01.2020	0	90	-90
35174	20.01.2020	0	5	-5
35174	21.01.2020	0	16	-16
35174	22.01.2020	0	16	-16
35174	23.01.2020	0	5	-5
35174	24.01.2020	0	1	-1
35174	27.01.2020	0	1	-1
35174	28.01.2020	0	2	-2
35174	29.01.2020	0	19	-19
35174	30.01.2020	0	2	-2
35174	31.01.2020	4	22	-18
35174	03.02.2020	0	6	-6
35174	04.02.2020	0	4	-4
35174	05.02.2020	0	6	-6
35174	06.02.2020	0	11	-11
35174	07.02.2020	1	9	-8
35174	10.02.2020	0	6	-6
35174	11.02.2020	0	12	-12
35174	12.02.2020	0	6	-6
35174	13.02.2020	0	6	-6
35174	14.02.2020	0	23	-23
35174	17.02.2020	0	5	-5
35174	18.02.2020	0	6	-6
35174	19.02.2020	0	13	-13
35174	20.02.2020	0	8	-8
35174	21.02.2020	1	4	-3
35174	24.02.2020	0	4	-4