Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Counting the number of days

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.

5 REPLIES 5
Frequent Visitor

Hi Everyone,

I found solution:

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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])).

Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

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

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors