topic Re: Counting Days in a column in DAX Commands and Tips

Counting Days in a column

phjz — Thu, 03 Nov 2022 12:58:18 GMT

Hey all,

its really hard to explain, so I'll try to show you what I want within the following table. What I want is a formula for the column "Number of Days".

As the table doesn't has values for every date, the formula for "Number of Days" has to refer to a calendar-table (I guess...), which is already created. Otherwise, you could say, that the date of the first apperance of a product could get the day number 1, but then I don't know how to do count the days that are missing in this table.

Vol	Date	Product	Number of Days
1	24.07.2022	A	1
2	24.07.2022	A	1
3	25.07.2022	A	2
2	26.07.2022	A	3
2	28.07.2022	A	5
1	29.07.2022	B	1

I hope this is understandable.

Thank you very much in advance!

Re: Counting Days in a column

Anonymous — Thu, 03 Nov 2022 13:05:49 GMT

hello phjz ,
please try :

Number of days = VAR basedate = CALCULATE(MIN(Table1[Date]),ALLEXCEPT(Table1,Table1[Product])) return DATEDIFF(basedate,SELECTEDVALUE(Table1[Date]),DAY) +1

Re: Counting Days in a column

phjz — Thu, 03 Nov 2022 14:07:23 GMT

That does work really good - thank you so much!

There is one last problem with this: I want to compare Product A and B using a line diagram, but I can't put the measure on the x-axis. When I try to type the formula in the field for a new column, it doesn't work. Do you have an idea for this?

Re: Counting Days in a column

v-easonf-msft — Mon, 14 Nov 2022 10:13:47 GMT

Hi, phjz

You can't apply a measure to the x-axis of line chart.

You need to convert your original measure formula to calculated column formula.

Column_Number of days = VAR basedate = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[Product] ) ) RETURN DATEDIFF ( basedate, Table1[Date], DAY ) + 1

Best Regards,
Community Support Team _ Eason